compare a value from 2 records
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 20
- Joined: Sun Dec 20, 2009 10:46 pm
compare a value from 2 records
Hello All,
In my requirement I need to compare a value from 2 records having one Key column.
For example:
Col1 Col2 Col3
1 A 4000
2 A 5000
.....
I need to compare the Col3 value from both records. Col2 is the Key Column.
If (Col3 value from the second record >= Col3 value from first record) then TRUE
ELSE " FALSE"
In this case the result is True.
Can you guys please help in handling this scenario?
In my requirement I need to compare a value from 2 records having one Key column.
For example:
Col1 Col2 Col3
1 A 4000
2 A 5000
.....
I need to compare the Col3 value from both records. Col2 is the Key Column.
If (Col3 value from the second record >= Col3 value from first record) then TRUE
ELSE " FALSE"
In this case the result is True.
Can you guys please help in handling this scenario?
-
- Premium Member
- Posts: 20
- Joined: Sun Dec 20, 2009 10:46 pm
Thanks for the response!mavrick21 wrote:You'd have to sort the records first based on your key column and then use Stage variables in a Transformer to do the comparison.
I followed the above flow and sorted the input based on KeyColumn(Col2) and created a KeyChangeColumn.
Below are the Stage variables that I created in my Transformer.
Name Derivations
Sv1 ---- If keyChange =1 then 1 else Sv1+1
Sv2 ---- If Sv1=1 Then input.Col3 Else ""
Sv3 ---- If Sv1 >1 Then input.Col3 Else ""
Sv4 ---- If Sv3>= Sv2 the "True" Else " False"
But I am getting wrong results. :(
Can you please let me know is this the correct way of assigning the Stage Variables?
You're wiping out the value of Sv2 when Sv1 <> 1. When you don't want to change the value of a stage variable, assign it it's own value. "" assigns the empty string value
Do this:
Sv1: (keep the same)
Sv2: if Sv1 = 1 then input.Col3 else Sv1
Sv3: if Sv1 > 1 then input.Col3 else ""
Sv4: (keep the same)
Alternately, for this purpose you could get rid of Sv1 altogether:
Sv2: if keyChange = 1 then input.Col3 else Sv1
Sv3: if keyChange = 0 then input.Col3 else ""
Sv4: (keep the same)
Regards,
Do this:
Sv1: (keep the same)
Sv2: if Sv1 = 1 then input.Col3 else Sv1
Sv3: if Sv1 > 1 then input.Col3 else ""
Sv4: (keep the same)
Alternately, for this purpose you could get rid of Sv1 altogether:
Sv2: if keyChange = 1 then input.Col3 else Sv1
Sv3: if keyChange = 0 then input.Col3 else ""
Sv4: (keep the same)
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Premium Member
- Posts: 20
- Joined: Sun Dec 20, 2009 10:46 pm
Thanks James!
The second method worked for me with Slight modification.
Code:
Sv1: If keyChange =1 Then input.Col3 Else Sv1
Sv2: If ( keyChange =0 ) Then input.Col3 Else input.Col3
Sv3: If Sv2>= Sv1 then "TRUE" Else "False"
Also in the Sort Stage I used Execution Mode as "Sequential"
Appreciate your help!
The second method worked for me with Slight modification.
Code:
Sv1: If keyChange =1 Then input.Col3 Else Sv1
Sv2: If ( keyChange =0 ) Then input.Col3 Else input.Col3
Sv3: If Sv2>= Sv1 then "TRUE" Else "False"
Also in the Sort Stage I used Execution Mode as "Sequential"
Appreciate your help!
Glad it worked!
Just 2 comments:
1) The () around the keyChange=0 shouldn't have been necessary, but isn't harming anything
2) Setting the execution mode of Sort to Sequential of course loses some of the advantage of the parallel engine. If your file is a relatively small one, no big deal. If parallel mode wasn't working correctly, then perhaps the data wasn't being partitioned correctly.
Regards,
Just 2 comments:
1) The () around the keyChange=0 shouldn't have been necessary, but isn't harming anything
2) Setting the execution mode of Sort to Sequential of course loses some of the advantage of the parallel engine. If your file is a relatively small one, no big deal. If parallel mode wasn't working correctly, then perhaps the data wasn't being partitioned correctly.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Premium Member
- Posts: 20
- Joined: Sun Dec 20, 2009 10:46 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Partition using a key-based partitioning algorithm on the first Sort key or, if this does not yield more distinct values than the number of nodes, on the first two Sort keys.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.