Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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

View solution in original post

yanivvl0
Creator III
Creator III
Author

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