Tutorials & Examples Web-help Blog

Split Column of CSV-Like Data into Columns and set there an X


#1

Hello Community,

I have following issue and kindly ask for support to solve it :slight_smile:

I have datasets in following format:

Number,Name,Attributes
1,Name 1,"Attr1,Attr2,Attr3"
2,Name 2,"Attr3,Attr4,Attr2"
3,Name 3,"Attr5,Attr1"

… and I want to transform it in this Format

Number,Name,Attr1,Attr2,Attr3,Attr4,Attr5
1,Name 1,X,X,X,,
2,Name 2,,X,X,X,
3,Name 3,X,,,,X

In other words: comma separated data in one column of a data set should be transformed into columns, and a flag should be set in each column according to the csv dataset in that one column.

If transformation is not possible, columns could be also predefined, like in Iterate dataset column by column.

Thank you for your help
Best Regards


Pivot-Like Behavior
#2

Hi @MaxEasy,

the result is clearly a matrix table which means that it should be pivoted from a straight table. And the straight table can be obtained by splitting Attributes in your source data into rows.

The less obvious moment here what aggregation function to use for pivoting – it should be the β€œAny” function.

See the example below:

split-pivot.morph (3.2 KB)
Book1.xlsx (9.4 KB)