parsing out excel row for count of specific values

[JohnSamuel123]

hi,

i am pulling data from a live excel file. this excel file is populated via Microsoft teams forms. one of the columns has a multiple choice where the user ticks all that applies:

snapshot of excel file:

ID	Reasons
1	Happy;Sad;Lunch;
2	Dinner;Happy;
3	Happy;
4	Sad;breakfast;No;

i want to display a bar chart for a count of all the values. so count of "Happy" would be 3. whereas Qlik sees 3 different counts for happy.

i want a field that counts the number of times each reason("Sad;", "No;", "Happy;" etc) is in the file so it can be easily populated in a graph,

how would i parse out this information? each value ends with a ";" if that helps

many thanks

[Or · Accepted solution]

The easiest way to handle this would typically be to first use Subfield() to break these values apart into rows, and then count whatever values you're interested in.

Load ID, Subfield(Reasons,';') as Reason

From YourFile;

You could then either have a table with Reason as the dimension and Count(ID) as the measure, or you could use explicit formulas such as Count( {< Reason = {'Happy'} >} ID) to count specific values.

[stevejoyce]

source_data:

load
*
from https://community.qlik.com/t5/New-to-Qlik-Sense/parsing-out-excel-row-for-count-of-specific-values/t...
(html, utf8, embedded labels, table is @1)
;

data_subfield:
load *
where Reason>0;
load
ID,
trim(subfield(Reasons, ';')) as Reason
resident source_data;

count_reason:
load
Reason, count(ID) as ReasonCount
resident data_subfield
group by Reason;

drop tables source_data, data_subfield;
exit script;