Compare value with previous record.

videsh77 · Post by **videsh77** » Tue Mar 06, 2007 12:51 am

How can I compare 2 records at the same time?

Prev rec A,B
Current rec B,C

col1 of current record if equals to col2 of prev record, I would like to redirect that value (in this case it is B) to other file.

How can we achieve this functionality using DataStage?

I thought of using lookup file stage, considering @OUTROWNUM, but it is aborting. I felt I might be wrong in the approach, can anyone suggest the right approach?

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 12:54 am

If you are checking for Duplicate records and trying to divert to other file, use Sort stage to sort based on a column and assign "CreateKeyChageColumn". Using Transformer or Filter, divert the records whose values are 0s.
This has been discussed several times in forum.

csrazdan · Post by **csrazdan** » Tue Mar 06, 2007 12:59 am

kumar_s wrote:If you are checking for Duplicate records and trying to divert to other file, use Sort stage to sort based on a column and assign "CreateKeyChageColumn". Using Transformer or Filter, divert the records whose values are 0s.
This has been discussed several times in forum.

Kumar,
Can you please explain what is "CreateKeyChageColumn"

videsh77 · Post by **videsh77** » Tue Mar 06, 2007 1:06 am

kumar_s wrote:If you are checking for Duplicate records and trying to divert to other file, ...This has been discussed several times in forum.

No I am not checking for duplicate records. In my file anywhere I can have col2 value of previous record could be same as col1 value of current processing record.

Also can you please pass on the single link for the previously discussed topic? I tried search with the string in subject line.

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 1:12 am

csrazdan wrote: Kumar,
Can you please explain what is "CreateKeyChageColumn"

Its the option available in Sort stage. Making this to True creates the column KeyChange. This column is set to 1 for the first row in each group where the value of the sort key changes. Subsequent records in the group have the column set to 0.

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 1:15 am

videsh77 wrote: No I am not checking for duplicate records. In my file anywhere I can have col2 value of previous record could be same as col1 value of current processing record.

Ok, you need to compare Previous value of Col2 to current value of Col1 or the vice versa.

User Transformer stage,

User stage variables

Code: Select all

vCheck = If Input.Col1 = vPreCol2 Then 1 Else 0
vPreCol2 = Input.Col2

Based on the condition of vCheck flag, you can divert the records to various links.

videsh77 · Post by **videsh77** » Tue Mar 06, 2007 4:32 am

Thanks, it worked.