need to split records into multiple records
Moderators: chulett, rschirm, roy
need to split records into multiple records
source file is like this:
EMPID~cpframeA~cptypeA~cpformatA~cpisleA~cpliteralA~cptermA~cpframeZ~cptypeZ~cpformatZ~cpisleZ~cpliteralZ~cptermZ~lpframeA~lptypeA~lpformatA~lpisleA~lpliteralA~lptermA~lpframeZ~lptypeZ~lpformatZ~lpisleZ~lpliteralZ~lptermZ~tryframe~trytype~tryformat~tryisle~tryliteral~tryterm~eqpframe~eqptype~eqpformat~eqpisle~eqpliteral~eqpterm
123~ABCX~MI~DD~EE~FF~A~ZXCS~MO~RR~QQ~SS~Z~ABDY~MQ~DWD~EQE~FEF~A~ZXCS~MA~RWR~QQQ~SES~Z~LPO~OP~PO~LI~KO~~LIO~IP~PI~LO~KQO~~
I want the output to look like this:
EMPID~frame~type~format~isle~literal~term
123~ABCX~MI~DD~EE~FF~A
123~ZXCS~MO~RR~QQ~SS~Z
123~ABDY~MQ~DWD~EQE~FEF~A
123~ZXCS~MA~RWR~QQQ~SES~Z
123~LPO~OP~PO~LI~KO~~
123~LIO~IP~PI~LO~KQO~~
Can you please me in resolving this
EMPID~cpframeA~cptypeA~cpformatA~cpisleA~cpliteralA~cptermA~cpframeZ~cptypeZ~cpformatZ~cpisleZ~cpliteralZ~cptermZ~lpframeA~lptypeA~lpformatA~lpisleA~lpliteralA~lptermA~lpframeZ~lptypeZ~lpformatZ~lpisleZ~lpliteralZ~lptermZ~tryframe~trytype~tryformat~tryisle~tryliteral~tryterm~eqpframe~eqptype~eqpformat~eqpisle~eqpliteral~eqpterm
123~ABCX~MI~DD~EE~FF~A~ZXCS~MO~RR~QQ~SS~Z~ABDY~MQ~DWD~EQE~FEF~A~ZXCS~MA~RWR~QQQ~SES~Z~LPO~OP~PO~LI~KO~~LIO~IP~PI~LO~KQO~~
I want the output to look like this:
EMPID~frame~type~format~isle~literal~term
123~ABCX~MI~DD~EE~FF~A
123~ZXCS~MO~RR~QQ~SS~Z
123~ABDY~MQ~DWD~EQE~FEF~A
123~ZXCS~MA~RWR~QQQ~SES~Z
123~LPO~OP~PO~LI~KO~~
123~LIO~IP~PI~LO~KQO~~
Can you please me in resolving this
Hi,
I couldn't quite get the logic for splitting the string (perhaps due to the fact I'm short on time).
The trick to achieve this goal will be to plant a @VM between each part.
besides that you need to define the key column you wish to be in each row occurance and feed the manipulated data to a hashed file.
then you need to read from this hashed file and define the "Normalize on" section in the top right of the output tab of your hashed file to specify the column with the @VM you planted.
Don't forget to specify this column's type as MV (Multi Value) in the columns tab
IHTH (I hope This Helps),
I couldn't quite get the logic for splitting the string (perhaps due to the fact I'm short on time).
The trick to achieve this goal will be to plant a @VM between each part.
besides that you need to define the key column you wish to be in each row occurance and feed the manipulated data to a hashed file.
then you need to read from this hashed file and define the "Normalize on" section in the top right of the output tab of your hashed file to specify the column with the @VM you planted.
Don't forget to specify this column's type as MV (Multi Value) in the columns tab
IHTH (I hope This Helps),
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Hi,
forgot to mention that if this is a fixed number of field you can also try the pivot stage
IHTH,
forgot to mention that if this is a fixed number of field you can also try the pivot stage
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why not just use a Sequential File stage, declare the right number of fields delimited by "~" plus one "dummy field, and specify "none" as the record terminator?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom