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.
Regarding Vertical Pivot
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Regarding Vertical Pivot
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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.
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.
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
No I am reading the data as
One record as one column
So no problem while reading..
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?
Shivaranjani
-
- Participant
- Posts: 147
- Joined: Sat Apr 30, 2005 1:23 am
- Location: Bangalore,India
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
I have a server job solution that should work for any number (Actually 99999999 ) of such fields:
Job is:
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.
Job is:
Code: Select all
SeqFile----->Transformer----->SeqFile(No delimiter;No quote;Unix Format)
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.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>