Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is there an expression in Qlik that can count the number of times each string appears in a delimited list for a bar chart? For example, I have a table that looks like the following (the delimited list column can contain a single string or a comma separated list):
ID | Category | Delimited List |
10001 | A | String 1 |
10002 | A | String 1, String 2, String 3 |
10003 | D | String 2, String 4 |
10004 | C | String 1, String 3 |
I can account for totaling the number of instances a single string appears with an expression like the following:
Count(if(not wildmatch([Delimited List], '*,*'), [Delimited List]))
However, I need to extend this expression to include totals for the number of times each string appears in a delimited list so I can accurately visualize the number of times each string appears in the column using a bar chart.
Hope that makes sense.
Cheers!
You can create one simple snowflake dimension like below
T1:
Load
ID,
Category,
"Delimited List"
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);
T2:
Load "Delimited List",
SubField("Delimited List",',') as List
Resident T1;
Now you can create a graph with
Dimension : List
Expression : Count(List)
So you want to create a bar chart with axis values string 1, string 2 and value will show corresponding count of string?
That's correct. The X-axis needs to show the correct number of times each string shows up in the example "Delimited List" column. The first bar would be "String 1", second bart "String 2", and so on. Thanks!
You can create one simple snowflake dimension like below
T1:
Load
ID,
Category,
"Delimited List"
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);
T2:
Load "Delimited List",
SubField("Delimited List",',') as List
Resident T1;
Now you can create a graph with
Dimension : List
Expression : Count(List)