Current Indicator for duplicate records

[kogads]

Hi All,

I have records which look like below

RecId Date
1 20100614
2 20100614
3 20100614
3 20100615
3 20100616

The output should look like

RecId Date Current Indicator
1 20100614 Y
2 20100614 Y
3 20100614 N
3 20100615 N
3 20100616 Y

Can any one please suggest a logic for this.

Thanks...

[anbu]

Use sort stage and set Create Key Change column to true which creates a field KeyChange.

Code: Select all

Current Indicator : If KeyChange = 1 then 'N' Else 'Y'

[kogads]

Use sort stage and set Create Key Change column to true which creates a field KeyChange.

Code: Select all

Current Indicator : If KeyChange = 1 then 'N' Else 'Y'

I used the sort stage with the logic you mentioned. i used the RecId as sort key and set the create key change column option. The output is not what iam expecting.

RecId Date Current Indicator
1 20100614 Y
2 20100614 Y
3 20100614 Y
3 20100615 N
3 20100616 N

this is the output i got. Instead i tried with 2 sort stages.In the 1st i sorted on Date in descending order with hash partition on recId and in 2nd sort stage I used same partition and sort key as RecId with create change key column option true.Then I got the right result as

RecId Date Current Indicator
1 20100614 Y
2 20100614 Y
3 20100614 N
3 20100615 N
3 20100616 Y

Can any one suggest how to get this result with just one sort stage instead of 2.

[anbu]

Set create change key column to true in the first sort. You dont need second sort to do this.

Then use

Code: Select all

Current Indicator : If KeyChange = 1 then 'Y' Else 'N'