split comma separated values in column into rows

[#1]

Hi,

I have data like below in excel

Group_Id	Items
1	Bread,Biscuit,Chocolate
2	Jean,Shirt,Shoes

I want to load above as below mentioned table structure in data model

Group_Id	Items
1	Bread
1	Biscuit
1	Chocolate
2	Jean
2	Shirt
2	Shoes

[trdandamudi · Accepted solution]

May be as attached:

Source_Data:

Load * Inline [

Group_Id,Items

1,"Bread,Biscuit,Chocolate"

2,"Jean,Shirt,Shoes"

];

NoConcatenate

LOAD

Group_Id,

SubField(Items,',') as Items

Resident Source_Data;

Drop Table Source_Data;

[antoniotiman]

Script

Table:
LOAD Group_Id,
SubField(Items,',') as Item
FROM
File
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

[vindhya_giri]

Hi Antonio,

I have 60 fields in my application out of which 32 of them are comma separated and I want each of those comma separated fields to be  split into different rows.

What is the best practice?

1. To use  SubField(Field1,',') as Field1  for each of the 32 fields?

2. To have the Subfield for each of those feilds done in the application level or to be done while generating the QVD?

    When I tried either of the option, I am getting error: Request ran out of memory

3. Does using the SubField() have any performance issue because of using it times in one application?

Please advise

Thanks

Regards

Vindhya

[Roman_S]

Hi,

What is I have way more unique items and combinations in one cell? How can I copy and load inline all of it? ot there is other solution?

[Sai_Mohan]

Hi,

As of this example, Subfield is working for me. Like showing comma seperated values in different rows. 

But, My Issue here is Iam getting duplicate values. each values twice.

For example: 

I used Subfield(Item,',') in Expression sheet. I am getting values like bread

bread

bread

biscuit 

biscuit ...etc..

Please help me with this

Thanks in advance. 

[AFE]

Does this work only in the load data space??

Can it work in the expression space for an individual visualization?

I tried plugging in '=SubField(Capitalize(Trim([Field 1])), ',')'.....but it only returned the 1st value in the {Bread,Biscuit,Chocolate} --> i.e, only returned Bread instead of outputting all 3 in separate rows

[AFE]

Does this work only in the load data space??

Can it work in the expression space for an individual visualization?

I tried plugging in '=SubField(Capitalize(Trim([Field 1])), ',')'.....but it only returned the 1st value in the {Bread,Biscuit,Chocolate} --> i.e, only returned Bread instead of outputting all 3 in separate rows