Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jamiekim
Contributor III
Contributor III

Counting Instances of Text within Data

Hello,

I have names of people in one Excel column and next to it,  a column named "Notes." The notes contain something like "Phone checked out to XXX" or "Phone owned by XXX", etc.  I would like to count all the names of the people that have "checkout" in their "notes" column.  I tried to do a combination of the Count and If functions, to no avail.

I would appreciate your help in solving this issue.

Thank You,

Jamie

 

Labels (1)
1 Solution

Accepted Solutions
usamabinsadiq
Contributor III
Contributor III

Hi Jamie,

 

I found the solution, please use this:

sum(if(wildmatch([Billing Title], '*checkout*'), 1, 0))

 

Regards, 

if my replay helped you then please press like button and do not forget to press the "Accept as Solution" button.

View solution in original post

8 Replies
Lisa_P
Employee
Employee

You could create a table with each person as a dimension, then add a measure using the FindOneOf function:

FindOneOf('checkout', notes) and this will give a 1 for all those that are true, then use the Sum as totals function in the measure properties to add them up

jamiekim
Contributor III
Contributor III
Author

Great suggestion in the right direction! This returns, however, every person whose notes contain any character of the word "checkout".... is there any way to make it only return those people that have the whole word "checkout" in their notes column?

Thank you!

usamabinsadiq
Contributor III
Contributor III

Hi,

I think this will help you:

sum(if(match(notes, 'checkout'), 1, 0))

 

Regards, 

if my replay helped you then please press like button and do not forget to press the "Accept as Solution" button.
jamiekim
Contributor III
Contributor III
Author

hmmm something is not working.  the exact field i'm looking at (column name) is "Billing Title" so i changed the formula to this and changed match to mixmatch because the the formula needs to NOT be case sensitive --

sum(if(mixmatch([Billing Title], 'checkout'), 1, 0))

any ideas on how to get this working?

 

Thank you!

usamabinsadiq
Contributor III
Contributor III

Hi,

can you provide some example data or screenshots of result its giving and describe what is not working?

if my replay helped you then please press like button and do not forget to press the "Accept as Solution" button.
jamiekim
Contributor III
Contributor III
Author

Hi, again,

Please see attached pictures. 

1-screenshot of the table and results in qlik

2-raw data in excel

3-the expression under "measures" in qlik

Hope this clarifies.  Please let me know if it doesn't!

Thank You,

Jamie

usamabinsadiq
Contributor III
Contributor III

Hi Jamie,

 

I found the solution, please use this:

sum(if(wildmatch([Billing Title], '*checkout*'), 1, 0))

 

Regards, 

if my replay helped you then please press like button and do not forget to press the "Accept as Solution" button.
jamiekim
Contributor III
Contributor III
Author

You are the best!!  That worked, thank you 🙂