topic Re: Split a features list into columns in Connectivity & Data Prep

Split a features list into columns

Anonymous — Thu, 16 Aug 2018 15:41:45 GMT

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:

And the final table would look like

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

Re: Split a features list into columns

petter — Thu, 16 Aug 2018 17:53:22 GMT

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
];

Re: Split a features list into columns

petter — Thu, 16 Aug 2018 19:10:51 GMT

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;

Re: Split a features list into columns

Anonymous — Fri, 17 Aug 2018 07:33:49 GMT

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

Re: Split a features list into columns

petter — Fri, 17 Aug 2018 19:14:47 GMT

You're welcome - happy to help.