split records

[mail2hfz]

Hi,

I have a requirement to split the sub records. Input contains following fields.
Col1 ->a1|a2|a3
Col2 ->abc
Col3->10
My output should be:
col1 col2 col3
a1 abc 10
a2 abc 10
a3 abc 10
I achieved this by using a transformer. But my col1 may contain upto 50 delimited values,which resluts in 50 links to transformer, so I was asked to go for an alternative approach. Can anyone suggest me an alternative approach to achieve this.

Thanks

[ray.wurlod]

In an upstream Transformer stage split a1 into 50 columns and preserve a2 and a3. Downstream of that use a Pivot stage.

[mail2hfz]

Appreciate your response. It works but we are not sure of the maximum columns to split. If we know the maximum value then we can split into so many columns and use pivot but we don't know the max value. I should have mentioned this while posting but I apologize for that. Is it a good way to form the incoming record into a vector and then use split sub vector. Will this work in this case?

Thanks

[sud]

You have to create a buildop where you have to count the number of delimiters and then spit out as many records as required.

[mail2hfz]

Any other approach besides build ops?

[ray.wurlod]

Same approach as I outlined earlier. Split into (MAX) columns, filling with in-band nulls, pivot, then filter out un-needed rows.