Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Highlighted
luism_rds
New Contributor

Split a features list into columns

Hello Everyone,

I'm writing because it has being very hard to find a solution to this issue even though i think it is a very common situation.

I'm loading some data coming from an excel file. One of the columns contains a set of features separated by spaces. My goal is to create one column for every possible feature stored in the original features column and that each of the new columns contains 1 or 0 in the case of that feature being present or not in a given row.

As an example, the original data would look like:

original.PNG

And the final table would look like

goal.PNG

I think it has to have something related to GENERIC but was not able to do it so far. Hope my explanation is explicit enough.

Thanks in advance

Luis

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Split a features list into columns

If you need to have the number of features and fields determined directly from the data table a more dynamic approach looks like this:

DATA:

LOAD * INLINE [

ID, FEATURES

1, A C D H

2, A B C D

];


FEATURES:

LOAD

' LOAD ID,SubStringCount(FEATURES,''' & Concat(DISTINCT FEATURE & Chr(39) &') AS ' & FEATURE,', SubStringCount(FEATURES,''') AS FEATURES;

LOAD

ID,

    SubField(FEATURES,' ') AS FEATURE

RESIDENT

  DATA;


vPrecedingLOAD = Peek('FEATURES',0,'FEATURES');

DROP TABLE FEATURES;


RESULT:

$(vPrecedingLOAD);

LOAD * RESIDENT DATA;


vPrecedingLOAD=;

DROP TABLE DATA;

4 Replies
MVP
MVP

Re: Split a features list into columns

If the number of features are fixed and well known this little load script should work well:

DATA:

LOAD

  ID,

  SubStringCount(FEATURES,'A') AS A,

  SubStringCount(FEATURES,'B') AS B,

  SubStringCount(FEATURES,'C') AS C,

  SubStringCount(FEATURES,'D') AS D,

  SubStringCount(FEATURES,'H') AS H

INLINE [

ID, FEATURES

1, A C D H

2, A B C D

];

MVP
MVP

Re: Split a features list into columns

If you need to have the number of features and fields determined directly from the data table a more dynamic approach looks like this:

DATA:

LOAD * INLINE [

ID, FEATURES

1, A C D H

2, A B C D

];


FEATURES:

LOAD

' LOAD ID,SubStringCount(FEATURES,''' & Concat(DISTINCT FEATURE & Chr(39) &') AS ' & FEATURE,', SubStringCount(FEATURES,''') AS FEATURES;

LOAD

ID,

    SubField(FEATURES,' ') AS FEATURE

RESIDENT

  DATA;


vPrecedingLOAD = Peek('FEATURES',0,'FEATURES');

DROP TABLE FEATURES;


RESULT:

$(vPrecedingLOAD);

LOAD * RESIDENT DATA;


vPrecedingLOAD=;

DROP TABLE DATA;

luism_rds
New Contributor

Re: Split a features list into columns

Thank you very much for your quick reply petter-s! First time I use the Qlik community.

MVP
MVP

Re: Split a features list into columns

You're welcome - happy to help.