Pivot Functionality

ramukds · Post by **ramukds** » Sun Jul 19, 2009 10:37 pm

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

etldwh_techie2050 · Post by **etldwh_techie2050** » Sun Jul 19, 2009 10:53 pm

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.

syed_haji_1201 · Post by **syed_haji_1201** » Sun Jul 19, 2009 11:59 pm

Hi,

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

Regards
H

shivakumar · Post by **shivakumar** » Mon Jul 20, 2009 4:53 am

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

ramukds · Post by **ramukds** » Mon Jul 20, 2009 6:15 am

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

chulett · Post by **chulett** » Mon Jul 20, 2009 6:27 am

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

ramukds · Post by **ramukds** » Mon Jul 20, 2009 8:47 am

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? ...

chulett · Post by **chulett** » Mon Jul 20, 2009 9:13 am

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?

ramukds · Post by **ramukds** » Mon Jul 20, 2009 9:50 am

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? ...

shivakumar · Post by **shivakumar** » Tue Jul 21, 2009 3:25 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 21, 2009 3:43 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2009 4:41 pm

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

DSXchange

Pivot Functionality

Pivot Functionality

Re: Pivot Functionality