Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yanivvl0
Creator III
Creator III

How to show occurrence of members comparatively?

hi to all qlicker's ,

i have NAMES and CITIES , I need to show comparatively numbers of visits of NAMES that visited in a CITY ,

with the others CITIES , for example , if we look at London we can see that Mira and Jecob visit there -> So its 2 ,

Now we check Mira and Jecob in Jerusalem and we see that they also visit there -> So its 2 ,

Now we check Mira and Jecob in New York and we see that only Mira visit there -> So its 1 ,

Now we check Mira and Jecob in Mumbai and we see that they also visit there -> So its 2 .     

   .. and so on ...  ( Mira and Hana and Jecob  visit Jerusalem  )

I need to show in a table or chart or anything the wanted Solution :

VISIT CITY.PNG

i know its hard - but its fun , THANKS.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Create a cartesian product:

T1:

LOAD * INLINE [

NAME, CITY

Mira,London

Mira,Jerusalem

Mira,Mumbai

Mira,New York

Hana,Jerusalem

Hana,Mumbai

Jecov,London

Jecov,Jerusalem

Jecov,Mumbai

]

;

JOIN (T1)

LOAD NAME as NAME, CITY as CITY2 RESIDENT T1;

Then use CITY and CITY2 as dimensions in a pivot table and count(CITY) as expression


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

Create a straight table with CITY as dimension and these for expressions:

  • London: count({<CITY,NAME=P({<CITY={'London'}>}NAME)>}NAME)
  • Jerusalem: count({<CITY,NAME=P({<CITY={'Jerusalem'}>}NAME)>}NAME)
  • New York: count({<CITY,NAME=P({<CITY={'New York'}>}NAME)>}NAME)
  • Mumbai: count({<CITY,NAME=P({<CITY={'Mumbai'}>}NAME)>}NAME)

talk is cheap, supply exceeds demand
yanivvl0
Creator III
Creator III
Author

hi Gysbert, i understand , it is a nice solution for now , but i have many cities and i need something dynamic , is it possible ?

Gysbert_Wassenaar

Create a cartesian product:

T1:

LOAD * INLINE [

NAME, CITY

Mira,London

Mira,Jerusalem

Mira,Mumbai

Mira,New York

Hana,Jerusalem

Hana,Mumbai

Jecov,London

Jecov,Jerusalem

Jecov,Mumbai

]

;

JOIN (T1)

LOAD NAME as NAME, CITY as CITY2 RESIDENT T1;

Then use CITY and CITY2 as dimensions in a pivot table and count(CITY) as expression


talk is cheap, supply exceeds demand
yanivvl0
Creator III
Creator III
Author

WOW , so simple and so smart - THANKS Gysbert  !!