Split variable strings based on a delimiter

[twanqlik]

I could use some advice for the following:

I'm trying to load test strings in a table that never have the exact same fields.

2 examples:

1) City=Helmond&gender=1&dateofbirth=1900

2) country=NL&answer1=true&car=Ferrari

The text strings are basically answers given on forms.

All the strings have an ID, but they are not unique, they are linked to the form, but those text strings also vary, it depends on how many answers are given. Example

Example: Form1

1) country=NL&answer1=true&car=Ferrari

2) country=GB&answer1=true&car=BMW&question1=test&question10=true

My plan was to make the user select a Form on the dashboard and that in a table the linked strings are splitted by the delimiter (&).

There are 2 kind of delimiters: & and =

& --> distinct between the combinations 'answer=question&answer=question'

= --> distinct between the question and the answer

My desired output when Form 1 is selected:

Example:

Country	Answer1	car	question1	question10
NL	true	Ferrari
GB	true	BMW	test	true

However, the output for Form 2 could be completely different!

Is this possible with Qlikview?

[jonvitale]

Twan,

Seems tricky, but interesting problem. I haven't done anything like this before but here's a rough sketch of what I might try.

1. You'll probably need to use some form of iteration to make this work. Basically, you'll have to run code for the maximum number of key-value pairs that you have in your expression. The SubStringCount function can count how many "&" have. This number + 1 is the maximum number of pairs, I believe. For example in your example 2 above, you have 4 "&", so you have 5 pairs. SubStringCount - script and chart function ‒ QlikView

2. My goal would be to make this a "long" table. At the end of the process I'd like something like this:

.... original line Key, Key, Value

....   1, country, GB

....   1, answer1, true

....

So, each original line will map to multiple lines in this new table.

To do this you'll need to repeat some code up to N times (from step 1), where you make a copy of the original table, use either SubField or TextBetween to get the next pieces of text between delimiters "&" and "=" and add them as key, value columns. Once you do this N times, you can bind all of these tables together to get the "long" table.

3. Use the "Generic Load" to go from a long table to a wide table. Here's a good resource.

The Generic Load

I think this approach should work, sorry I can't put together an exact solution for you, but good luck.

[evan_kurowski]

  Agree with the generic load approach.

[URL_PARAMS]:
LOAD * INLINE [
URL_PARAMS
  country=NL&answer1=true&car=Ferrari
  country=GB&answer1=true&car=BMW&question1=test&question10=true
];


[ALL_PARAMS]:
LOAD URL_PARAMS, Subfield(PARAMS,'=',1) AS FIELD, Subfield(PARAMS,'=',-1) AS VALUE;
LOAD URL_PARAMS, Trim(Subfield(URL_PARAMS,'&')) AS PARAMS RESIDENT [URL_PARAMS];  


[GENERIC]:  
GENERIC LOAD URL_PARAMS AS HANDLE, FIELD, VALUE RESIDENT [ALL_PARAMS];

 

[evan_kurowski]

Expanded keying to attribute multiple url strings to single FORM

 
[URL_PARAMS]:
LOAD * INLINE [
FORM, URL_PARAMS
ALPHA, country=NL&answer1=true&car=Ferrari
ALPHA, country=GB&answer1=true&car=BMW&question1=test&question10=true
BETA, zoologist=Doolittle&country=NL&answer1=true&car=Ferrari
BETA, avian_type=owl&food_preference=pemmican&answer1=false&car=BMW&question1=test&question10=true
];


[ALL_PARAMS]:
LOAD FORM, Subfield(PARAMS,'=',1) AS FIELD, Subfield(PARAMS,'=',-1) AS VALUE;
LOAD FORM, Trim(Subfield(URL_PARAMS,'&')) AS PARAMS RESIDENT [URL_PARAMS];  


[GENERIC]:  
GENERIC LOAD FORM, FIELD, VALUE RESIDENT [ALL_PARAMS];

 

[swuehl · Accepted solution]

Maybe just use Subfield() function(s) to separate your key / value pairs.

I wouldn't use GENERIC to create fields from the KEY values, just use a pivot table in the front end to create your desired layout.

[URL_PARAMS]:

LOAD *, Recno() as RecID INLINE [

FORM, URL_PARAMS

ALPHA, country=NL&answer1=true&car=Ferrari

ALPHA, country=GB&answer1=true&car=BMW&question1=test&question10=true

BETA, zoologist=Doolittle&country=NL&answer1=true&car=Ferrari

BETA, avian_type=owl&food_preference=pemmican&answer1=false&car=BMW&question1=test&question10=true

];

LOAD RecID, Subfield(KeyValue, '=',1) as Key, Subfield(KeyValue, '=',1) as Value;

LOAD RecID, Subfield(URL_PARAMS, '&') as KeyValue

Resident URL_PARAMS;

edit: sorry, typo

LOAD RecID, Subfield(KeyValue, '=',1) as Key, Subfield(KeyValue, '=',2) as Value;

LOAD RecID, Subfield(URL_PARAMS, '&') as KeyValue

Resident URL_PARAMS;

[evan_kurowski]

This method works too, I suppose it's a matter of whether you would like each parameter to have its own field, or having them all collected in one field and separated by the visualization.  When i use this approach, it is one less script step, but gets trickier on the ui side when applying multiple selections, as many fields have been bundled into one. 

[twanqlik]

This is perfect, thank you!

evan_kurowski‌ also thank you very much, I both want to give you the best answer!

[evan_kurowski]

No worries, I'm used to not getting credit.  I find other ways to recoup the r&d costs