how to get the result in Transformer
Moderators: chulett, rschirm, roy
how to get the result in Transformer
Hi,
I have file like
INPUT(Entire row in a single column) only one input column
1,2,3,4,A,B,C,D
but i Required the
OUTPUT(4 rows are in a single column) only one output column
1,2,3,4,A
1,2,3,4,B
1,2,3,4,C
1,2,3,4,D
Please tell me the way to do in the PX
I have file like
INPUT(Entire row in a single column) only one input column
1,2,3,4,A,B,C,D
but i Required the
OUTPUT(4 rows are in a single column) only one output column
1,2,3,4,A
1,2,3,4,B
1,2,3,4,C
1,2,3,4,D
Please tell me the way to do in the PX
Last edited by deesh on Sat Dec 01, 2012 11:31 pm, edited 1 time in total.
This would a good example of either a PIVOT or, if you have 8.7, use of transform stage looping.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
If you are trying with loop you may try the below-
1.In stage variable-2.Loop While - 3.In loop variable derivation-4.OUtput col deriavtion-
1.In stage variable-
Code: Select all
col1 SV1
Code: Select all
@iteration<=Dcount(SV1,",")-4
Code: Select all
Field(SV1,",",4+@iteration) LV1
Code: Select all
Col1:",":LV1 Col1
Re: how to get the result in Transformer
how to do it apart from looping concept
Hi Deesh,
You may try with a transformer and a pivot stage.
1.In transformer output derivation, 1,2,3,4 into a column and A,B,C,D in four different columns.
2. In pivot stage you can take the first column as key column(1,2,3,4) and pivot the 4 columns into a single column.
Note- The above is possible only if the number of columns to be pivoted is 4.
You may try with a transformer and a pivot stage.
1.In transformer output derivation, 1,2,3,4 into a column and A,B,C,D in four different columns.
2. In pivot stage you can take the first column as key column(1,2,3,4) and pivot the 4 columns into a single column.
Note- The above is possible only if the number of columns to be pivoted is 4.
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi prasson_ibm,
I think the:
in transformer output link should be-
Please correct me if I am wrong
I think the:
Code: Select all
field(in.col,",",1)
Code: Select all
in.col[1,4]:",":field(in.col,",",5) link1
in.col[1,4]:",":field(in.col,",",6) link2
in.col[1,4]:",":field(in.col,",",7) link3
in.col[1,4]:",":field(in.col,",",8) link4
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The first four columns are the Pivot key, the other columns are the pivoting columns. Provided your data are correctly partitioned, that's all you need.
Last edited by ray.wurlod on Sun Dec 02, 2012 11:38 am, edited 1 time in total.
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.
bhasds - I really don't understand your fascination with the field() function, which practically all of your suggestions include. The input example posted has eight columns in it, there's absolutely no need to read it as a single string and manually parse them out.
And actually Ray, the first four are the pivot keys (bhasds: you can have more than one) while the last four are the pivoting columns. Otherwise as noted it's just a plain old horizontal Pivot. Save the transformer looping for something that actually requires it.
Now, as noted, this is all predicated on the example posted being representative of the actual problem.
And actually Ray, the first four are the pivot keys (bhasds: you can have more than one) while the last four are the pivoting columns. Otherwise as noted it's just a plain old horizontal Pivot. Save the transformer looping for something that actually requires it.
Now, as noted, this is all predicated on the example posted being representative of the actual problem.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
chulett wrote:And actually Ray, the first four are the pivot keys (bhasds: you can have more than one) while the last four are the pivoting columns. Otherwise as noted it's just a plain old horizontal Pivot. Save the transformer looping for something that actually requires it.
Post edited appropriately.
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.
Please don't take that as any kind of comment that you shouldn't help people here - we appreciate everyone who pitches in with the problems that people post. I'm just saying to save the field() function for when it is appropriate... as in actually needed. That's all I was trying to communicate, thanks for understanding.bhasds wrote:I will try not to think field() function in every post and reading the records as single string.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers