DSXchange

I have a situation where a field in the data row in the source is a string delimited by a ','.

eg:

COL1 COL2
1 1, 2, 3, 4, 5
2 A, B, C, D
3 g, h, i

Here is what I am trying to achieve:

count the number of occurences ',' and create an individual row.

eg:

COL1 COL2
1 1
1 2
1 3
1 4
1 5
2 A
2 B
2 C
2 D
3 g
3 h
3 i

Can the Pivot stage supports this type of parsing?

Any advise on acheiving this is appreciated.

Pivot pivots, you need to parse. If you know the max # of fields, parse out that single field to the max, pivot and then filter out any nulls. No counting required.

chulett wrote:Pivot pivots, you need to parse. If you know the max # of fields, parse out that single field to the max, pivot and then filter out any nulls. No counting required. ...

Thanks Chulett. So it can be done using pivot if I set the threshold(max to a number. Say, 20). For my eralier sample data set, how do I define the derievation in the Pivot stage.

Any help is appreciated!

Pivot stage pivots. There are no derivations. Any derivations, including parsing, need to be done in an upstream Transformer stage.

The 'derivation' in the Pivot stage just becomes a comma delimited list of the column names to pivot, as noted in the documentation.

chulett wrote:The 'derivation' in the Pivot stage just becomes a comma delimited list of the column names to pivot, as noted in the documentation. ...

Can you please share an example to achieving parsing out a single field to multiple rows if I set a max number?

Thanks

There are examples (or at least an example) in the online help for the stage.