Split one row to multiple rows

[venkatesan]

Hi,

I am calling the routine in the transformer the output of the routine is
a:12,b:123,c:12345,d:9 ..........

The Job design is : Source Table --> Transformer --> Hash File

Finally once the job is successfully run the out put of the hash file is

ColumnA , Column B

JOBNAME a:12,b:123,c:12345,d:9 ..........


Now i want to chage the output of the hash file to

COLUMNA , COLUMN B

JOBNAME , a:12
JOBNAME , b:123
JOBNAME , c:12345
JOBNAME , d:9

Can you guys please let me know how to solve this issue.

[samyamkrishna]

You can use a pivot stage to this.

in the derivation tab give Col1,Col2,Col3.

[svhari76]

You Can use soem thing like
Job1:
Source Table-->Tranformer-->Sequencialfile(not hash file)

Job2:
Sequential file -->PIvot stage -->hashfile

You can even combine Job1 and 2 in a single job.

In you sequential file if you have something like a:1,b:12,c:123 ...
and if you know the max number of fields you can use
col1,col2,col3,..col20 for the derivation in pivot stage.

I am not sure how this works for indefinite number of columns.

[DSguru2B]

Convert the commas with @VM character and write it to a hashed file. While reading from the same hashed file, normalize on the second column.

[venkatesan]

Thanks Dsguru2B,

I had converted the commas using @VM Character It is giving total number of records which are split by commas. But, can you please throw a light on how to do normalize on second column

[DSguru2B]

When you go into the Output tab/Columns tab of the hashed file you will see a column called 'Type', double click it, from the drop down chose 'MV'. The 'Normalize On' option, previously greyed out, should now be availbe. Choose your column that you set 'MV' on from that drop down as well. Hit view data. Your data should now be normalized.

[chulett]

FYI... you need to use an 'Account-based' hashed file to have this option, from what I recall.

[DSguru2B]

Externally pathed hashed file works too.

[chulett]

As long as you create a VOC record for it or even without one?

[DSguru2B]

Even without one.

[ray.wurlod]

Even without one.

[chulett]

So... even without one?

[DSguru2B]

Thats funny.

[venkatesan]

When you go into the Output tab/Columns tab of the hashed file you will see a column called 'Type', double click it, from the drop down chose 'MV'. The 'Normalize On' option, previously greyed out, should now be availbe. Choose your column that you set 'MV' on from that drop down as well. Hit view data. Your data should now be normalized.

Thanks a lot.. It worked :D

[venkatesan]

How can i implement the same logic if the design consists Universe table --> Transformer -->Target Table .

Here How to apply normalize on target table