Count of (distinct) values belonging to a list

[Anonymous]

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.

[saurabhwadhwa]

Hi ,

Pls check the attached app . Hope this helps you .

BR

Saurabh

[Anil_Babu_Samineni]

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);

[Anonymous · Accepted solution]

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)