Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Award | 2018 | 2019 | 2020 |
Prestigious Award |
John Charles |
John Adam Peter |
Peter |
Junior Award | Jolene |
Hercules Joel |
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).
- Marcus
Try as below;
Crosstable(Yearlabel,Names,1)
Temp:
Load Award,2018,2019,2020
From yoursource;
Main:
Load
Award
,Yearlabel
, Subfield (Names,chr(10)) as Names
Resident Temp;
Drop Table Temp;
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?
Appreciate the help!
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?
Appreciate it!
TMP:
CrossTable(Years, People)
LOAD F1 as Award,
[2018],
[2019],
[2020]
FROM file.xlsx(ooxml, embedded labels, table is Sheet1)
Where Len(F1)>0;
Table:
LOAD
Award,
People,
Years,
Award &' '& Years as AwardID // This is the point!
Resident TMP;
DROP Table TMP;
Expression: Count(distinct AwardID)
Alternative: Count(distinct Award &' '& Years)
After doing the the above transformation you can count the number of awards in a chart as below
dimension
=Names
Measure
=count(Award)
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.
- Marcus