Regarding Vertical Pivot

G SHIVARANJANI · Post by **G SHIVARANJANI** » Wed Oct 03, 2007 3:20 am

Hello ,

I have input file with variable fields as shown bello

1,a1,a2
2,b1,b2,b3,b4
3,c1,c2,c3

I need the result as

1,a1
1,a2
2,b1
2,b2
2,b3
2,b4
3,c1
3,c2
3,c3

Tryied to find the result from vertical pivot....search ...

But failed as the input file has dynamic fields

Please suggest..

thanks.

Maveric · Post by **Maveric** » Wed Oct 03, 2007 3:25 am

What you need is a horizontal pivot. Use pivot stage. Specify all four fields in the derivation of your output field in the Pivot Stage. Then use a filter stage to filter out all the nulls in the output field and pass only the records with value.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Wed Oct 03, 2007 3:32 am

Hi,
But the maximum limit is not known....

the number of columns can be any valuee as shown

1,a1,a2 :its 3 here
2,b1,b2,b3,b4 :its 5 here
3,c1,c2,c3 :its 4 here

It could be N such columns in any record.

Thanks.

Maveric wrote:What you need is a horizontal pivot. Use pivot stage. Specify all four fields in the derivation of your output field in the Pivot Stage. Then use a filter stage to filter out all the nulls in the output field and pass only the records with value.

Maveric · Post by **Maveric** » Wed Oct 03, 2007 3:47 am

But to read the data you would be having N columns, N being the max number of columns. Is my understanding correct? i.e,

1,a1,a2,Null,Null
2,b1,b2,b3,b4
3,c1,c2,c3,Null

Is this how your data will be?

G SHIVARANJANI · Post by **G SHIVARANJANI** » Wed Oct 03, 2007 3:56 am

No I am reading the data as

One record as one column

So no problem while reading..

Maveric wrote:But to read the data you would be having N columns, N being the max number of columns. Is my understanding correct? i.e,

1,a1,a2,Null,Null
2,b1,b2,b3,b4
3,c1,c2,c3,Null

Is this how your data will be?

Raghavendra · Post by **Raghavendra** » Wed Oct 03, 2007 5:20 am

Do you have any delimeters if so is there any max number for the delimiters.

ameyvaidya · Post by **ameyvaidya** » Wed Oct 03, 2007 7:56 am

I have a server job solution that should work for any number (Actually 99999999

) of such fields:

Job is:

Code: Select all

SeqFile----->Transformer----->SeqFile(No delimiter;No quote;Unix Format)

The seq file is read in as a single row as is being done now.
there are 3 stage variables in the transformer:
V1=field(<in>.Col,",",1)
V2=field(<IN>.Col1,",",2,99999999)
V3= Ereplace(V2,",", char(10):V1:",")

The ourput is 1 column with derivation:

<Out>.Col=V1:",":V3

The output file when read as a unix file will have the required format.

Dunno if this can be done in PX, do not have the access to check.

Hope this helps.