DSXchange

Hello,
Can Pivot be used for an example like below;

Input

Field 1, Field2
p1 u1,u2
p2 u3,u4

Note: Field 2 has multiple values seperated by ','.


Required Output

Field1 Field2
p1 u1
p1 u2
p2 u3
p2 u4

ramku, horizontal pivot is only possible and not the vertical. You have to apply some other logic but cannot use pivot stage for your purpose.

Hi,

Just Make use of stage variable and after that use pivot stage.

Regards
H

Hi ,

Use the Column Import Stage in that Import Input Column=Field2 and in the Output define the columns as Filed3 and Filed4 and in the Output Format Tab final delimeter as 'none' and filed defalts as 'Comma' after that use the pivot stage.

In this method no need to use the Transformer.

Flat File ----->Column Import Stage--- >Pivot Stage--->Flat File.

Regards
Siva

Shiva,

Field 2 is dynamic. It could have values u1,u2,u3....

shivakumar wrote:Hi ,

Use the Column Import Stage in that Import Input Column=Field2 and in the Output define the columns as Filed3 and Filed4 and in the Output Format Tab final delimeter as 'none' and filed defalts as 'Comma' after that use the pivot stage.

In this method no need to use the Transformer.

Flat File ----->Column Import Stage--- >Pivot Stage--->Flat File.

Regards
Siva

'Dynamic' is fine as long as you know or can define a maximum number of columns to pivot - can you?

Craig,

Thats the issue...we dont know the maximum...it could vary.

chulett wrote:'Dynamic' is fine as long as you know or can define a maximum number of columns to pivot - can you? ...

So you can't even make a reasonable guess as to how many it might have as a maximum? I understand it will vary, that's fine, you're certain there is no effective upper boundary?

Craig,
I was trying make it as scalable as possible. Any ways if we define 5 as the upper boundary, I guess the data has to be appended with 3 commas
to the following row inorder to read it. Correct me if I am wrong if you think there is other workaround

Incoming:p1 u1,u2.

Data Modification to read it: p1 u1,u2, , , .

chulett wrote:So you can't even make a reasonable guess as to how many it might have as a maximum? I understand it will vary, that's fine, you're certain there is no effective upper boundary? ...

Hi,

Without knowing the Max Count it is not possible to define the output columns..

If you are thinking that the max values you can receive for the Field2 as 5 then define 5 output columns in the column import stage and propagate the columns to the pivot stage..

Cheers
Siva

You have options to choose from - something like

1.) using external script like awk
2.) crossing with a dummy row generator and to extract individual values
3.) build op

4.) Using Sort stage, Transformer stage and Remove Duplicates stage.