Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

split comma separated values in column into rows

Hi,

I have data like below in excel

Group_IdItems
1Bread,Biscuit,Chocolate
2Jean,Shirt,Shoes

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

Group_IdItems
1Bread
1Biscuit
1Chocolate
2Jean
2Shirt
2Shoes
1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

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;

View solution in original post

5 Replies
trdandamudi
Master II
Master II

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
Master III
Master III

Script

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

Regards,

Antonio

vindhya_giri
Creator II
Creator II

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
Contributor
Contributor

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
Contributor III
Contributor III

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.