Hi please help me on the below requirement.....
My source is like
CustNo CustName
1 rajesh,naga,prabhu,jil,ray,chullet..........n
Output I need like
CustNo CustName
1 rajesh
1 naga
1 prabhu
1 jil
1 ray
1 chullet
. .
. .
. .
. .
1 n
How to achieve this in DataStage PX 7.5?
Row into columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Re: Row into columns
Hi All,
This can be easily done in Datastage 8.5.
Step 1:- Read the source as it is in single column.
Step2:-
Define two stage veriables .
Define Loop Condition as:-
Loop Derivation:-
Step3:- Create Two Columns in Output tab of transformer:-
Col1=Svar1
Col2=RcdTyp
I hope this will work.
This can be easily done in Datastage 8.5.
Step 1:- Read the source as it is in single column.
Step2:-
Define two stage veriables .
Code: Select all
Svar1=Field(incol," ",1)
Svar2=Field(incol," ",2)
Code: Select all
AITERATION=1 Or RemainingRecord=""
Code: Select all
InitRecord=If @ITERATION = 1Then Svar2 Else RemainingRecord
Rcd=Field(InitRecrod, ",",1)
RcdLength=Len(RcdTyp)
RemainingRecord=InitRecrod [RcdLength+1,9999]
Col1=Svar1
Col2=RcdTyp
I hope this will work.
Try this....
row generator 1: one row with one column name "text", varchar datatype, value: "1 sam,alex,george,danny,mark"
transformer 1: 3 columns
col1: Field(DSLink11.text, " ",1)
col2: Field(DSLink11.text, " ",2)
dummy:0 key col
row generator 2: 2 columns generate number of records=10 (maximum that can come from source, in your case 'n')
num: datatype integer generate value: initial value=1, increment=1, limit=1000000 (maximum that can come from source, in your case 'n')
dummy: datatype integer generate value: initial value=0, increment=0, limit=0
join: key dummy
join type: inner
output 3 columns
col1: from source
col2: from source
num: from lookup
transformer 2: stage variable - sv33: Field(DSLink21.col2, ",", DSLink21.num)
output cols:
col1: from source
col2: sv33 derived from stage variable
output constraint: Len( sv33)>0
Run the job and check the output:
Peek_22,0: col2:sam col1:1
Peek_22,0: col2:alex col1:1
Peek_22,0: col2:george col1:1
Peek_22,0: col2:danny col1:1
Peek_22,0: col2:mark col1:1
======================================
If you can write a small shell script you can save lot of time for yourself.
Hint: use sed or tr command
Enjoy!
Code: Select all
. row generator 2
. |
. |
. |
. |
. V
row generator 1 ------> transformer 1 --------> join --------> transformer 2 ----------> peek
transformer 1: 3 columns
col1: Field(DSLink11.text, " ",1)
col2: Field(DSLink11.text, " ",2)
dummy:0 key col
row generator 2: 2 columns generate number of records=10 (maximum that can come from source, in your case 'n')
num: datatype integer generate value: initial value=1, increment=1, limit=1000000 (maximum that can come from source, in your case 'n')
dummy: datatype integer generate value: initial value=0, increment=0, limit=0
join: key dummy
join type: inner
output 3 columns
col1: from source
col2: from source
num: from lookup
transformer 2: stage variable - sv33: Field(DSLink21.col2, ",", DSLink21.num)
output cols:
col1: from source
col2: sv33 derived from stage variable
output constraint: Len( sv33)>0
Run the job and check the output:
Peek_22,0: col2:sam col1:1
Peek_22,0: col2:alex col1:1
Peek_22,0: col2:george col1:1
Peek_22,0: col2:danny col1:1
Peek_22,0: col2:mark col1:1
======================================
If you can write a small shell script you can save lot of time for yourself.
Hint: use sed or tr command
Enjoy!
Code: Select all
row generator 2
|
|
|
|
|
V
row generator 1 ------> transformer 1 --------> join --------> transformer 2 ----------> peek
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Define sufficient columns. Default unpopulated columns to NULL and filter these downstream of the Pivot stage.skp wrote:but in the i/p column there is no limit of the values. In this case what can i do.... please help...
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.