Stage variables

[samsuf2002]

Hi All,

I have requirement where like

Code: Select all

KEY1,KEY2,COL3 
1,A,200
1,A,200
1,A,200
1,A,300

I need to check if value of COL3 changes for same KEY1 and KEY2 then I need to reject the entire existence of KEY1 KEY2 value like in the example since 1,A has different value of COL3 therefore 1,A should be excluded from further processing.

My attempt was creating 4 stage variables as
lastKEY1,lastKEY2,lastCOL3, EXCLUDE

Code: Select all

EXCLUDE as "if lastKEY1 = KEY1 and lastKEY2 = KEY2 and lastCOL3 = COL3 then 1 else 0

Now using EXCLUDE in constraint to filter out the KEYS where COL3 changes but even if there no change in COL3 the output has one less records for same KEY...looks like for the first record for any key its not finding the last values and assigning EXCLUDE as 0 ....

I am running the transformer in sequential mode.

Any suggestions will be appreciated.

[ray.wurlod]

Why not use a Sort stage on all three columns and generate a Key Change column?

[HariK]

Use copy stage with two outputs
1--> remove duplicates on all three cols --> transformer with constraint & filter u menioned --> join stage
2 --> Join stage, join on key1, key2