Parse question

[ririr]

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.

[chulett]

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.

[ririr]

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!

[ray.wurlod]

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

[chulett]

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

[ririr]

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

[chulett]

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