HI All,
please share how to use the combine stage for the below scnenairo
source :
id | column
1|10
1|20
1|30
2|10
2|50
3 |10
4|20
target
1|10,20,30
2|10,50
3|10
4|20
Combine stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
Combine stage
Last edited by vishal_rastogi on Fri Jul 08, 2011 5:57 am, edited 1 time in total.
Vish
this is called vertical pioveting, can be done by using two sort stages and 1 transformer stage
1st sort stage - sort the databased on key column and enable key change column
2nd sort stage - use 'dont sort previosuly sorted' data on key column and sort key change field in ascending order.
3rd transformer stage - Use # stage variables ( no if variable should be same as no of max values you can have for 1 keys) and one stage variable to hold key change. one stage variable to hold counter for example
svCount initilize with zero = if svPreKeychange = 1 then 0 else svCount+1
svField1 = if svCount =0 then inputCol else ' '
svField2 = if svCount =1 Then inputCol Else ' '
svField3 = If svCount=3 Then inputCol Else ' '
....
...
svPreKeychange (initialize with zero) = KeyChange
after this you can use these stage variable values to assign to columns
try with this , may be in some place you need to tweek this solution.
1st sort stage - sort the databased on key column and enable key change column
2nd sort stage - use 'dont sort previosuly sorted' data on key column and sort key change field in ascending order.
3rd transformer stage - Use # stage variables ( no if variable should be same as no of max values you can have for 1 keys) and one stage variable to hold key change. one stage variable to hold counter for example
svCount initilize with zero = if svPreKeychange = 1 then 0 else svCount+1
svField1 = if svCount =0 then inputCol else ' '
svField2 = if svCount =1 Then inputCol Else ' '
svField3 = If svCount=3 Then inputCol Else ' '
....
...
svPreKeychange (initialize with zero) = KeyChange
after this you can use these stage variable values to assign to columns
try with this , may be in some place you need to tweek this solution.
Regards,
Deepak Singhal
Everything is okay in the end. If it's not okay, then it's not the end.
Deepak Singhal
Everything is okay in the end. If it's not okay, then it's not the end.
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can you be more specific about which stage type you are referring to?
Then read the chapter on this stage in the manual, and report back to us what it does, and why you believe it should be able to perform a vertical pivot.
Then read the chapter on this stage in the manual, and report back to us what it does, and why you believe it should be able to perform a vertical pivot.
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.