FIELD function

agathaeleanor · Post by **agathaeleanor** » Fri Jan 21, 2011 4:57 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 21, 2011 5:08 am

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.

agathaeleanor · Post by **agathaeleanor** » Tue Jan 25, 2011 1:43 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 25, 2011 4:00 am

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