Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
JohnSamuel123
Creator
Creator

parsing out excel row for count of specific values

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

Labels (4)
1 Solution

Accepted Solutions
Or
Champion
Champion

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.

View solution in original post

2 Replies
Or
Champion
Champion

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
Specialist II
Specialist II

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;