How to Compare Current Record with Previous record

pavithravenky · Post by **pavithravenky** » Fri Aug 03, 2012 2:50 pm

Hi,

I have a Key Columns that contains duplicates records. I need to generate Key whenever the value of the Key Column Changes. How to achive this using transformer stage and not surrogate key stage.

Example:
Id Addr
1 a1
1 a2
1 a3
2 b1

Result Expected:
Id Addr AId
1 a1 01
1 a2 01
1 a3 01
2 b1 02

R
Pav

jwiles · Post by **jwiles** » Fri Aug 03, 2012 2:56 pm

The simplest method would be to generate a keyChange column in a sort stage preceding the transformer. keyChange = 1 when a record has a new key value, 0 when the same as the previous record.

When you see keyChange = 1, generate a new Key otherwise use the same key as for the previous record.

Regards,

ray.wurlod · Post by **ray.wurlod** » Fri Aug 03, 2012 4:23 pm

Welcome aboard.

There are over 250,000 answers to be found on DSXchange, including the answer to your question. In the menu at the top of the page is a link to the Search facility. (Tip: prefer Exact Search whenever possible.)

srds2 · Post by **srds2** » Sat Aug 11, 2012 10:13 am

you can get expected output using transformer stage variables. create 3 stage variables.

var1: if ID = var2 then A else B
var2: ID
var3: if var1 = B then var3+1 else var3

assign default value for var3 as 0

ray.wurlod · Post by **ray.wurlod** » Sat Aug 11, 2012 4:59 pm

That's an overly complex solution. It can be done readily with two stage variables. The only thing that is important is the order of execution (a) of the stage variables and (b) of the data (what does "previous" mean when the data are partitioned?).

chulett · Post by **chulett** » Sat Aug 11, 2012 5:38 pm

Based on the example posted, it looks to me like you've already got your "AId" field in the "Id" field. Can we get a more realistic example, please? One that shows why you would need an additional field.