Split a features list into columns

[Anonymous]

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

[petter]

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

[petter · Accepted solution]

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;

[Anonymous]

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

[petter]

You're welcome - happy to help.