Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
irina_oancea
Contributor
Contributor

Count of (distinct) values belonging to a list

Hi,

I have the following data:

NameFood
JohnApple
JohnBread
JohnApple
MaryBanana
MaryButter
MaryBread
MaryOrange
DanBread
DanButter

List of fruits:

Fruits
Apple
Banana
Orange

How can I get the count of distinct fruits pro Person? Like below:

NameCount of Fruits
John1
Mary2
Dan0

Thanks a lot for your help.

1 Solution

Accepted Solutions
irina_oancea
Contributor
Contributor
Author

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)

View solution in original post

3 Replies
saurabhwadhwa
Partner
Partner

Hi ,

Pls check the attached app . Hope this helps you .

BR

Saurabh

Anil_Babu_Samineni

Check if that helps?

Capture.PNG

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
irina_oancea
Contributor
Contributor
Author

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)

View solution in original post