need to split records into multiple records

HemaV · Post by **HemaV** » Mon Jul 13, 2009 1:13 am

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

roy · Post by **roy** » Mon Jul 13, 2009 1:28 am

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),

roy · Post by **roy** » Mon Jul 13, 2009 1:32 am

Hi,
forgot to mention that if this is a fixed number of field you can also try the pivot stage
IHTH,

datskosaraju · Post by **datskosaraju** » Mon Jul 13, 2009 2:06 am

Search for "vertical pivot"

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jul 13, 2009 2:15 am

I think this is just Pivot and not "Vertical" pivot.

HemaV · Post by **HemaV** » Mon Jul 13, 2009 2:23 am

using pivot stage which column i need to take as key column

datskosaraju · Post by **datskosaraju** » Mon Jul 13, 2009 2:26 am

Sainath.Srinivasan wrote:I think this is just Pivot and not "Vertical" pivot. ...

My bad........ I apologize for the mistake. Yes its "horizontal pivot"

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jul 13, 2009 5:46 am

Hema,

Not being rude, can you please explain what you tried so far so that others can help from there.

Key is the column which identifies / groups same record. So use the technique to find which column must be key.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 13, 2009 4:48 pm

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?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 14, 2009 1:55 am

Ray,

The main fields occur only once and 'additional attributes' occur multiple times. So it is not just wrapping the rows to columns.