Breaking a key-value pair in a dictionary into table rows/columns in Qlik Sense

[wanderingcatto]

I have a csv file with data that looks like this:

Branch	{Manager:SalesVolume}
North	{'Adam':47, 'Bernard':68, 'Calvin':36}
East	{'Derrick':29, 'Eugene':37, 'Fabian':20, 'Gayle':43}

However, I want the tables in my Qlik Sense dashboard to have:

- A filter with all of the branches ('North' and 'East')

- A table that looks like this

Manager	Sales Volume
Adam	47
Bernard	68
Calvin	36
-	-

(This above example table assumes the user selected 'North' as the branch on the filter field. If the user selected 'East', the values in the table should change accordingly)

How can I break up the key-value pair in the original csv file into a table form within Qlik Sense? I note that there is a split function within the Data Manager but it is only limited to 9 delimiters, and I can't seem to split by both ":" and "," here.

[MarcoWedel · Accepted solution]

Hi, you could try using the Data Load Editor like this instead (replacing the inline load with your csv):

table1:
Load Branch,
     TextBetween(SubField(ManagerSalesVolume,':',1),Chr(39),Chr(39)) as Manager,
     SubField(ManagerSalesVolume,':',2) as SalesVolume;
Load Branch,
     SubField(TextBetween([{Manager:SalesVolume}],'{','}'),',') as ManagerSalesVolume
Inline [
Branch	{Manager:SalesVolume}
North	{'Adam':47, 'Bernard':68, 'Calvin':36}
East	{'Derrick':29, 'Eugene':37, 'Fabian':20, 'Gayle':43}
](delimiter is '\t');

[wanderingcatto]

Hi MarcoWedel,

Thanks! While your syntax works, can I seek to understand (just for my knowledge):

- Why is there a need to do the load statement twice?

- The second load statement returns whatever that's inside '{}', so that's actually the "first step". So why is this the second load statement instead of the first load statement (I've tried to swap the sequence and the load would fail)

[MarcoWedel]

Hi,

glad it worked for you.

This concept is called preceding load and actually is executed in reverse order:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/load-data-from....