Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Jamie,
I found the solution, please use this:
sum(if(wildmatch([Billing Title], '*checkout*'), 1, 0))
Regards,
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
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!
Hi,
I think this will help you:
sum(if(match(notes, 'checkout'), 1, 0))
Regards,
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!
Hi,
can you provide some example data or screenshots of result its giving and describe what is not working?
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
Hi Jamie,
I found the solution, please use this:
sum(if(wildmatch([Billing Title], '*checkout*'), 1, 0))
Regards,
You are the best!! That worked, thank you 🙂