comma seperated values in rows

[surajap123]

Hi,

I have a table of data in qlikview where some fields have values that are comma seperated.

I want each comma seperated value to be in seperate rows in the field. The screenshot shows only 2 fields Names, Cities, but the real data has 7 fields. 

When i try to use subfield function like below it works for the 1st field, but from 2nd field it does many to many cartesian product.

SubField(Names, ',') AS Name

Can you please suggest.

[JandreKillianRIC]

Hi @surajap123 

I don't see the screenshot? 

Regards Jandre

[surajap123]

sorry. Just attached.

[JandreKillianRIC]

Hi @surajap123 

Is there any relation to the ID1, Alice vs the City, Just running a Subfield on both you will get 9 values for ID1. (3 Names multiplied by 3 Cities) 

Can you perhaps give your expected outcome / table? 

Regards Jandre

[surajap123]

Hi JandreKillianRIC,

The output you have shown is exactly what i am expcting. Could you provide the code. Thanks

Preview

[JandreKillianRIC]

Hi @surajap123 

You might need to drop the Temp table if do you need the original data stuff. 

See below 

Temp:

LOAD

    ID,

    Names,

    Cities

FROM [lib://desktop/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempNew:

Load 

ID, 

    SubField(Names, ',') as Name,

    SubField(Cities, ',') as City

Resident Temp;

Regards Jandre

[ali_hijazi]

use Subfield for both Name and Cities fields

[surajap123]

Thanks. When this table links is connected to other table in the model, it shows cartesion product(many to many). 

Thanks for your help with the table.

[JandreKillianRIC]

Hi @surajap123 

Glad this worked for you. 

Please mark the reply that contains the solution as a solution to "close" this community thread. 

Regards Jandre