Hi I have a situation where I need to count multiple values within the same cell like in the table below. The end result is to show a chart and the count and breakdown of awardees over the years.
I will be loading the values by an excel, and I would prefer not to hardcode.. Any Advice please?
You may use substringcount() on the delimiter-char which is probably a line-break + 1 and maybe some further if-loops to consider empty cells and/or any conditions.
But usually it's more suitable to load the data properly - means using a crosstable-load to resolve the crosstable-structure and then applying a subfield-loop on the field-values to create for each sub-value an own record. And afterwards it would be just something like: count(Name).
Hi Marcus, appreciate the quick response. I understand what you trying to achieve and have resolved the crosstable-load portion already. Any advise on how i should be applying a subfield-loop in Qlik Sense?
Hi Vineeth, appreciate the quick response. I manage to do something similar but i will need to know how many awards has a person won so I think I will need a way to loop through the "Names" and populate a new row in the data load editor. Any advise?
It's like @vinieme12 showed it in his example - the first parameter in subfield() takes the fieldname, the second the delimiter-char (by line-breaks usually chr(10) or chr(13) or both in combination) and without specifying a third parameter it creates an internal load-loop and new records for each sub-string within the value.
