transform rows to columns end record value to null
Moderators: chulett, rschirm, roy
transform rows to columns end record value to null
Dear Gurus please guid me on the following
source ----- - ------- Target
Cust Id ----- -------- Cust Id1 ,Cust Id2
1 ---------------------- 1 , 2
2 ----------------------3 , 4
3 ---------XFM ------- 5 , 6
4 ---------------------- 7, Null
5
6
7
I have above source table with different cust Id , I want target with to subsequent cust id as pair
If the job reach end of the record it shuold padd null as shown above
Please guid me on the same
Thanx in Advance
source ----- - ------- Target
Cust Id ----- -------- Cust Id1 ,Cust Id2
1 ---------------------- 1 , 2
2 ----------------------3 , 4
3 ---------XFM ------- 5 , 6
4 ---------------------- 7, Null
5
6
7
I have above source table with different cust Id , I want target with to subsequent cust id as pair
If the job reach end of the record it shuold padd null as shown above
Please guid me on the same
Thanx in Advance
Last edited by rhaddur on Mon Apr 02, 2007 11:01 pm, edited 3 times in total.
Rhaddur
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your requirement is not clear. Your words and your example do not match.
Please document the algorithm by which 1 generates 1,2; 2 generates 3,4; 3 generates 4,6 (note that 4 is repeated); 4 generates 7,NULL and 5,6 and 7 do not generate anything.
It would appear (if the result for 3 is a typo, and should be 5,6) that the two values can be generated using:
2 * @INROWNUM -1
2 * @INROWNUM
But you would need to know in advance what the highest key value in the file is, so as to know when to stop generating values. This could be obtained, for example, using tail -1 filename and processing that result.
Each derivation of the second column would need to check that the expression has not generated a larger value, and a constraint expression would need to be used to specify when to stop generating output rows.
Please document the algorithm by which 1 generates 1,2; 2 generates 3,4; 3 generates 4,6 (note that 4 is repeated); 4 generates 7,NULL and 5,6 and 7 do not generate anything.
It would appear (if the result for 3 is a typo, and should be 5,6) that the two values can be generated using:
2 * @INROWNUM -1
2 * @INROWNUM
But you would need to know in advance what the highest key value in the file is, so as to know when to stop generating values. This could be obtained, for example, using tail -1 filename and processing that result.
Each derivation of the second column would need to check that the expression has not generated a larger value, and a constraint expression would need to be used to specify when to stop generating output rows.
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: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This can acomplished by using transformer stagevariable -> "Previous value" property and sorting data if required. Logic is to save the count of records processed and increment the count and split the records into 2 different streams.
In server job you can do this with out any additional settings.
In Prallel job you to restrict the job to 1 node (Sequential run).
In server job you can do this with out any additional settings.
In Prallel job you to restrict the job to 1 node (Sequential run).