Turn a field storing a list into rows?

James T Sidebotham

Hi, is it possible to turn a list stored in a column into rows? For example:

ID COL

1 AA,BB,CC

2 XX,YY,ZZ

Becomes

1 AA

1 BB

1 CC

2 XX

2 YY

2 ZZ

Thanks

Dan Mason

Hi James, one solution could be to use the 'extract from list' function - You can import the attached dmxd to see an example of this.

Turn a field storing a list into rows.dmxd

Josh Boxer

Hi James

The value 'AA,BB,CC' is a list and there are a load of List Functions that are really powerful and under utilized:

https://docs.experianaperture.io/data-quality/hosted-aperture-data-studio/data-studio-objects/functions/#list-transformation~native-functions 'List count' and 'Extract from list'

To 'create' more rows use a Union step (and for future reference to create more columns use a Splice step).

James T Sidebotham

Great thanks, I'll have a play with it.

James T Sidebotham

I get the logic, but not sure how the original ID is kept and assigned to the new rows in my example (note that the list could be anything from 1 to 50 values). The way I am doing it is to concat the ID number to the COL value, after they have been extracted from the list. Then after the union, move the ID out of the COL value into a new ID field.

James T Sidebotham

Looks like I dent even need to do what I thought for the ID. Just including it in the mapping and union works fine.