DSXchange

I am aware of the function FIELD in transformer that

FIELD(Lnk_InLoad.COLUMN1,';',1) will return the first value store before semicolon.

However, I have a requirement which a column contained of multiple value separated by pipe. There is no fixed number of value will store within the column.

header col1","col2","col3","col4
eg. 001","GRH","DR|PH|JH|ST","2007

above sample data is delimiter with double-quote-comma-double-quote.
And the multiple value column is DR|PH|JH|ST

How do I achieve in such that i will need to separate one row of data into several rows depend on the number of value stored in the column (col3)

Thanks in advance for your help.

Can't you stop people providing you with such crappily structured files?

Field(InLink.TheString, "|", 1, 1) gives the first field from the delimited string, Field(InLink.TheString, "|", 2, 1) gives the second field, and so on. Parse into separate fields then Pivot.

I've no choice but accepted the crappy file structure

My bad, i should search the forum on horizontal pivot... but my DS has no pivot license.

Am trying to look for a solution by using routine.

There's no such thing as a Pivot license. Your DataStage does have a Pivot stage and may, indeed, have a PXPivot state too.