Count in a table with two fields related to another table

[qw_jakub]

Hello,

i need to count in a table which contains two fields (secondary keys), which are both linked to another table. Each field must be counted separatly. A simple example:

SessionID	Entry_Gate	Exit_Gate
1	A	A
2	A	B
3	B	B
4	C	D
5	D	C
6	E	C
7	B	E
8	C	E
9	A	B
10	A	D

Gate_ID	Gate_Name
A	Gate_A
B	Gate_B
C	Gate_C
D	Gate_D
E	Gate_E

I would like to get a table which shows how many times was each gate used as an Entry_Gate and how many times as an Exit Gate.

Following this discussion http://community.qlik.com/thread/75611?tstart=0 I have changed the script for the second table to

Gates:
LOAD Gate_ID as Entry_Gate,
 Gate_ID as Exit_Gate,
     Gate_Name
FROM
gates.xls
(biff, embedded labels, table is [List1$]);

But now, using Gate_Name as a dimenssion, I can not figure out the right expression.

count(Distinct SessionID)

counts of course only the sessions where Entry_Gate = Exit_Gate.

aggr(count(DISTINCT SessionID), Entry_Gate)

returns the right value but only for the gates, with at least one session where Entry_Gate = Exit_Gate, the rest is not calculated.

Thanks in advance for any advice or helpful hints.

A sample file with this example is attached.

Jakub

[#2 · Accepted solution]

Hi,

In Pivot chart click on Dimension Tab "Check Supress Null Values", then you see all possible entrygate count.

Hope it helps.

Best of luck

[qw_jakub]

This seems to do the job well.

Thanks a lot for Your help!

[kaushiknsolanki]

Hi,

     Try this script.

Sessions:
LOAD SessionID,
     Entry_Gate as Gate_ID,
     'Entry' as Status
FROM
sessions.xls
(biff, embedded labels, table is [List1$]);

Concatenate

LOAD SessionID,
     Exit_Gate as Gate_ID,
     'Exit' as Status    
FROM
sessions.xls
(biff, embedded labels, table is [List1$]);

Gates:
LOAD Gate_ID ,
  Gate_Name
FROM
gates.xls
(biff, embedded labels, table is [List1$]);

Then create a chart with dimension " Gate_Name " and expression as

Count({<Status = {"Entry"}>}Distinct SessionID)

Count({<Status = {"Exit"}>}Distinct SessionID)

Regards,

Kaushik Solanki