Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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');
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');
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)
Hi,
glad it worked for you.
This concept is called preceding load and actually is executed in reverse order: