Page 2 of 2
Posted: Fri Apr 05, 2013 12:09 pm
by 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.
Posted: Fri Apr 05, 2013 12:43 pm
by hargun
i done the follwoing changes for counter is it right.
stggroup:if DSLink24.keyChange='1' and stgprev='1' then 2 else stggroup +1
Posted: Fri Apr 05, 2013 1:49 pm
by 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
Posted: Fri Apr 05, 2013 3:33 pm
by ray.wurlod
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"
Posted: Sat Apr 06, 2013 11:48 am
by hargun
adityavinay wrote: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.
Posted: Sat Apr 06, 2013 5:12 pm
by adityavinay
hargun wrote: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.