Splitting data from one column and duplicating rows based on that

Hi,

 

In the link below there is an image of a table in which there is a column called “resources”.

This “resource” column has different data separated by ; (eg. actor_1; actor_2)

https://docs.google.com/file/d/0B7g4r5o6kel4b2c3Smo3M2o2Ync/edit?usp=sharing

 

I need to split the resources and duplicate the rows based on the amount of resources.

For instance, “actor_1; actor_2” would generate 2 rows; “actor_1; actor_2; actor_3” would generate 3 rows, and so on.

The new table would be like this one:

https://docs.google.com/file/d/0B7g4r5o6kel4eGZoQ0NiWGVfb0E/edit?usp=sharing

 

Thanks in advance for any help,

Cadu

Hi Cadu,

 

you need two nodes for this task.

First you need to split your value column(D) into many columns containg each one of the columns. The Cell Splitter is designed for this task. Select ; as the delimiter.

Afterwards you need the Unpivoting node.  Select all newly generated columns D_Arr[*] in the value columns Include List. And all the previous ones (A,B,C) in the retained Column List. You must also check the skip rows containing missing cells checkbox.

 

Cheers, Iris

1 Like

simple but useful.

1 Like