Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Load data with delimiter?

I am now stuck with a data load problem. I am trying to load a column of data that is separated using a comma. Is it possible?

Here is the sample data -


NameSports
JoeBaseball, Football
BillBowling, Football, Baseball
FredTennis, Golf
AlexGolf, Football

So I want to load sports of different people and be able to visualize for example how many people like baseball?


thanks for any help.



Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Load data with delimiter?

So I did find a solution for this, during the load statement -

LOAD
    Name,
        if(index(Sports,',')=0,Null(),SubField(Sports,',',1))as Sports_1,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',2))as Sports_2,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',3))as Sports_3,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',4))as Sports_4
FROM 'lib://Scratch/11111sports.xlsx'
(ooxml, embedded labels, table is Sheet1);
2 Replies
Not applicable

Re: Load data with delimiter?

So I did find a solution for this, during the load statement -

LOAD
    Name,
        if(index(Sports,',')=0,Null(),SubField(Sports,',',1))as Sports_1,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',2))as Sports_2,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',3))as Sports_3,
    if(index(Sports,',')=0,Null(),SubField(Sports,',',4))as Sports_4
FROM 'lib://Scratch/11111sports.xlsx'
(ooxml, embedded labels, table is Sheet1);
Employee
Employee

Re: Load data with delimiter?

It is much easier (and cleaner) if you omit the third parameter of SubField():

Load

     Name,

     Trim(SubField(Sports,',')) as Sport

From ...

HIC

Community Browser