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: 
m_dirksz
Contributor II
Contributor II

How to count values from two joined qvd-files?

Hello everybody,

I have to tables in two different qvd-files. When creating a new app I let them join.

Table1 contains the OrderNr and Date.

Table 2 contains multiple rows per OrderNr.

I can join OrderNr from the two tables.

Now I want to have the amount rows of OrderNr (so the multiple rows per OrderNr)

In SQL I did:

SELECT Table1.date, count (*) FROM Table1, Table2 WHERE Table1.OrderNr = Table2.OrderNr

GROUP BY Table1.date

And my returns give me:

2017-11-01 | 496

When I add Date as a dimension in Qlik Sense and select Count(OrderNr) as measure I only get this:

01/11/2017 | 29

This is de same as I get when I type the following SQL:

SELECT Table1.date, count (distinct.Table2.OrderNr) FROM Table1, Table2 WHERE Table1.OrderNr = Table2.OrderNr

GROUP BY Table1.date

How can I get the first (496) result?

Thanks!

1 Solution

Accepted Solutions
sarahplymale
Creator
Creator

In Qlik, it is best practice not to count on a key field (OrderNr in this case).  You can get unexpected results like you are seeing here where you want to count each row, but it is showing only the distinct count and vice versa.  If you add a counter field and then sum up that counter field the results will be more predictable.  For example:

Table1:

Load

Date,

OrderNr

From ...

Table2:

Load

OrderNr,

OrderDetail

1 as OrderCounter

From ...

Then your expression would be sum(OrderCounter), and it should give you the number of rows on Table2.  This should work even if you join tables 1 and 2 in Qlik.

Sarah

View solution in original post

5 Replies
sarahplymale
Creator
Creator

In Qlik, it is best practice not to count on a key field (OrderNr in this case).  You can get unexpected results like you are seeing here where you want to count each row, but it is showing only the distinct count and vice versa.  If you add a counter field and then sum up that counter field the results will be more predictable.  For example:

Table1:

Load

Date,

OrderNr

From ...

Table2:

Load

OrderNr,

OrderDetail

1 as OrderCounter

From ...

Then your expression would be sum(OrderCounter), and it should give you the number of rows on Table2.  This should work even if you join tables 1 and 2 in Qlik.

Sarah

ishanbhatt
Creator II
Creator II

Hi,

In QlikSense or QlikView please don't use your key field in the calculation. Make duplicate field of your key field or use

1 as counter_TableName.

Thanks.

Chanty4u
MVP
MVP

try to create  flags in your each table

and front end you can use the  flag in expression

count({<Flag={'0'}>}dim)

count({<Flag={'1'}>}dim)

m_dirksz
Contributor II
Contributor II
Author

Thanks you for your reaction. It worked!

m_dirksz
Contributor II
Contributor II
Author

I don't really understand this quite yet. Thanks for helping me out though.