hi,
I Have an input file containing a single pipe-delimited column and I want to be able to convert the pipe delimited 'subcolumns' into 'real' columns then pivot on them - the catch is I dont know before hand how many of the subcolumns there are
e.g.
source
Key DelimCol
A 1|4|5
B 7|8
C 9
target
A 1
A 4
A 5
B 7
B 8
C 9
As mentioned I cant be sure how many of the 'subcolumns' there are going to be
There are only a few thousand source records so performance not an issue
I believe the answer may be in the restructure staqges in PX (solution can be server or PX) but not to sure of best way forward
Thanks in advance
Yet another Pivot Question (bit different though)
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Server solution that will scale to any number of sub-columns:
IN the transformer, have 2 output columns
Firstkey<--- In.key
Data---< ereplace(in.DelimCol,"|",char(10):in.key:",")
In the seqfile input link, set the following format:
Delimiter=","
Quote Char=000
IN the SeqFile Output, creater 2 columns:
Key,
Col
With the following format:
Delimiter=","
Quote Char=000
This has been tested to work.
I do not have a corresponding PX solution
IHTH
Code: Select all
Source----->Transformer----->SeqFile----->Output
Firstkey<--- In.key
Data---< ereplace(in.DelimCol,"|",char(10):in.key:",")
In the seqfile input link, set the following format:
Delimiter=","
Quote Char=000
IN the SeqFile Output, creater 2 columns:
Key,
Col
With the following format:
Delimiter=","
Quote Char=000
This has been tested to work.
I do not have a corresponding PX solution
IHTH
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>