Page 1 of 1
Regarding Vertical Pivot
Posted: Wed Oct 03, 2007 3:20 am
by G SHIVARANJANI
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.
Posted: Wed Oct 03, 2007 3:25 am
by Maveric
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.
Posted: Wed Oct 03, 2007 3:32 am
by G SHIVARANJANI
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.
Posted: Wed Oct 03, 2007 3:47 am
by Maveric
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?
Posted: Wed Oct 03, 2007 3:56 am
by G SHIVARANJANI
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?
Posted: Wed Oct 03, 2007 5:20 am
by Raghavendra
Do you have any delimeters if so is there any max number for the delimiters.
Posted: Wed Oct 03, 2007 7:56 am
by ameyvaidya
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.