logic required

[dpeixotto]

You will need a sort with keyChange and a counter into transformer stage so you can add 1 into the group No based on your KeyChange value

Just be cautious about your partition and parallelism degree when adding an increment into the counter stage variable.

[hargun]

i done the follwoing changes for counter is it right.

stggroup:if DSLink24.keyChange='1' and stgprev='1' then 2 else stggroup +1

[adityavinay]

hargun,

If you are using version 8.5 or latest, try this way.

File -> sort stage-> trans->file

Create Key change column in sort stage , sort ascending on phone number.

In transformer, create two stage variables
sv1: LastRowInGroup(Phone number) - This will return 1 if it is last row in group
sv2:if input.keyChange =1 then sv2 +1 else Sv2 - this will create a group number.

map sv2 to groupnumber field and

derive family field as
If input.keyChange = 1 And Sv1 =1 Then "N" Else "Y".

This wil give you desired output. This works only if you are working on 8.5 or latest, we dont have LastRowInGroup() function in earlier versions

[ray.wurlod]

Code: Select all

Boolean = 1

is one of my pet peeves. You don't need the "= 1". DataStage expressions do support a Boolean context. Therefore, in this example, the stage variable (TinyInt) derivation can use input.keyChange alone (its value is only ever 1 or 0).

Code: Select all

If input.keyChange Then sv2 + 1 Else sv2

Similarly

Code: Select all

If inputKeyChange And LastRowInGroup(Phone) Then "N" Else "Y"

[hargun]

hargun,

If you are using version 8.5 or latest, try this way.

<snip>

This is not working, it will not increment the Group for different Phone number.

[adityavinay]

This is not working, it will not increment the Group for different Phone number.

I've tested it and its working fine as per your requirement. Make sure you are sorting and creating key change column only on Phone number.