Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data:
Name | Food |
---|---|
John | Apple |
John | Bread |
John | Apple |
Mary | Banana |
Mary | Butter |
Mary | Bread |
Mary | Orange |
Dan | Bread |
Dan | Butter |
List of fruits:
Fruits |
---|
Apple |
Banana |
Orange |
How can I get the count of distinct fruits pro Person? Like below:
Name | Count of Fruits |
---|---|
John | 1 |
Mary | 2 |
Dan | 0 |
Thanks a lot for your help.
Thanks a lot for your answers and sorry for my delay.
As I don't want to load the values separately (they are loaded directly from a database), I have used following solution proposed by somebody else from Qlik Community:
=Count({< Food = {$(=Chr(34)&Concat(Distinct Fruits,'","')&Chr(34))} >} TOTAL <Name> Distinct Food)
Hi ,
Pls check the attached app . Hope this helps you .
BR
Saurabh
Check if that helps?
Fruit:
Mapping LOAD Fruits, RowNo() as ID
FROM
[https://community.qlik.com/thread/318048]
(html, codepage is 1252, embedded labels, table is @2);
Fact:
LOAD Name,
RowNo() as ID,
ApplyMap('Fruit', Food, 'NA') as Food
FROM
[https://community.qlik.com/thread/318048]
(html, codepage is 1252, embedded labels, table is @1);
Thanks a lot for your answers and sorry for my delay.
As I don't want to load the values separately (they are loaded directly from a database), I have used following solution proposed by somebody else from Qlik Community:
=Count({< Food = {$(=Chr(34)&Concat(Distinct Fruits,'","')&Chr(34))} >} TOTAL <Name> Distinct Food)