Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master II
Master II

Re: split comma separated values in column into rows

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
Highlighted
Master II
Master II

Re: split comma separated values in column into rows

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

Highlighted
Master III
Master III

Re: split comma separated values in column into rows

Script

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

Regards,

Antonio

Highlighted
Creator II
Creator II

Re: split comma separated values in column into rows

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

Highlighted
Contributor
Contributor

Re: split comma separated values in column into rows

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?

Highlighted
Contributor III
Contributor III

Re: split comma separated values in column into rows

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.