Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Parsing a String Field and placing values into other fields in the same table

We import data from Salesforce tables. Some of those tables include Multi Picklist values that come in as text strings separated by a ";"

eg: one record could have a field value 'value 1;value 2;value 4'. Another record could have the field value  'value 3;value 4;value 5'

I would like create a load script that will  "explode" these multi picklist values into multiple fields inside my Qlik Table.

If my data in Salesforce looks like this:

Case Record IdCase Reason
Case 1Value 1;Value 2;Value 4
Case 2Value 1;Value 3;Value 4
Case 3Value 1;Value 2;Value 3;Value 5

I would like to get a table that looks like this:

Case Record IdValue 1Value 2Value 3Value 4Value 5
Case 111010
Case 210110
Case 311101

One thing i should say is that my list of possible values in my picklist is preset - i know what they are and therefore the structure of my final table is preset and does not need to be dynamic.

I have gone down the path of trying to run a For Next statement to parse  the Case Reason string but i don't know how to include the results inside  a load statement or how to update a resident table with the final details.

Any help veryvery appreciated

Helene

3 Replies
MVP
MVP

Re: Parsing a String Field and placing values into other fields in the same table

Try Subfield() in your LOAD:

LOAD

     "Case Record Id"

     Subfield("Case Reason",';') as "Case Reason"

FROM ....;

This will create a record per value. It's often better to not built a cross table as you described above, but if you absolutely need it, you can add GENERIC LOADs

The Generic Load

Not applicable

Re: Parsing a String Field and placing values into other fields in the same table

Thank you very much for your timely answer. I am trying this out.

On your last comment, As I have a "case" table which has one line per case record and each case can have multiple reasons, are you suggestion I create another table called "case reason" with the case record id as the join field ?

Thanks. H

Sent from my iPhone

MVP
MVP

Re: Parsing a String Field and placing values into other fields in the same table

Yes, might be a good idea, especially to avoid duplication of other facts in your case table.

Community Browser