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.
logic required
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 32
- Joined: Tue Dec 13, 2011 7:27 am
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
Boolean = 1
Code: Select all
If input.keyChange Then sv2 + 1 Else sv2
Code: Select all
If inputKeyChange And LastRowInGroup(Phone) Then "N" Else "Y"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 32
- Joined: Tue Dec 13, 2011 7:27 am