compare a value from 2 records

partheev123 · Post by **partheev123** » Tue Feb 08, 2011 2:02 pm

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?

mavrick21 · Post by **mavrick21** » Tue Feb 08, 2011 2:15 pm

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.

mavrick21 · Post by **mavrick21** » Tue Feb 08, 2011 2:21 pm

This might help

viewtopic.php?t=87243

partheev123 · Post by **partheev123** » Tue Feb 08, 2011 5:17 pm

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.

Thanks for the response!

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?

jwiles · Post by **jwiles** » Tue Feb 08, 2011 8:57 pm

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,

partheev123 · Post by **partheev123** » Wed Feb 09, 2011 2:05 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!

jwiles · Post by **jwiles** » Wed Feb 09, 2011 6:22 pm

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,

partheev123 · Post by **partheev123** » Sun Feb 13, 2011 3:39 pm

Hi,

I see the issue is with the partitioning in Sort Stage.
Can you please suggest me what kind of partitioning type is preferred if the data is passing through Sort Stage?
I am getting correct output when I use the Execution Mode as "Sequential".

ray.wurlod · Post by **ray.wurlod** » Sun Feb 13, 2011 7:01 pm

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.