how to get the result in Transformer

[deesh]

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

[ArndW]

This would a good example of either a PIVOT or, if you have 8.7, use of transform stage looping.

[bhasds]

If you are trying with loop you may try the below-

1.In stage variable-

Code: Select all

col1    SV1

2.Loop While -

Code: Select all

@iteration<=Dcount(SV1,",")-4

3.In loop variable derivation-

Code: Select all

Field(SV1,",",4+@iteration)  LV1

4.OUtput col deriavtion-

Code: Select all

Col1:",":LV1   Col1

[deesh]

how to do it apart from looping concept

[bhasds]

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.

[prasson_ibm]

Hi,
This can also be achieved by using transformer and funnel stage.
In output tab of transformer take 4 links.In every link write below derivation,

Link1:- field(in.col,",",1)

Then funnel all links that will be your desired output.


Thanks
Prasoon

[bhasds]

Hi prasson_ibm,

I think the:

Code: Select all

field(in.col,",",1)

in transformer output link should be-

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

Please correct me if I am wrong

[ray.wurlod]

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.

[chulett]

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.

[bhasds]

Hi Craig,

I will try not to think field() function in every post and reading the records as single string.

[ray.wurlod]

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.

[chulett]

I will try not to think field() function in every post and reading the records as single string.

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.