topic Re: split comma separated values in column into rows in QlikView

split comma separated values in column into rows

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: split comma separated values in column into rows

trdandamudi — Sun, 19 Jun 2016 11:59:05 GMT

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;

Re: split comma separated values in column into rows

antoniotiman — Sun, 19 Jun 2016 12:03:59 GMT

Script

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

Regards,

Antonio

Re: split comma separated values in column into rows

vindhya_giri — Thu, 09 Aug 2018 17:36:22 GMT

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

Re: split comma separated values in column into rows

Roman_S — Tue, 14 Jan 2020 17:09:23 GMT

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?

Re: split comma separated values in column into rows

Sai_Mohan — Tue, 30 Jun 2020 14:42:21 GMT

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

biscuit

biscuit ...etc..

Please help me with this

Thanks in advance.

Re: split comma separated values in column into rows

AFE — Mon, 09 Sep 2024 12:31:17 GMT

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

Re: split comma separated values in column into rows

AFE — Mon, 09 Sep 2024 12:32:06 GMT