Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

m_dirksz
New Contributor

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
Contributor

Re: How to count values from two joined qvd-files?

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

5 Replies
sarahplymale
Contributor

Re: How to count values from two joined qvd-files?

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

Re: How to count values from two joined qvd-files?

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
Esteemed Contributor III

Re: How to count values from two joined qvd-files?

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
New Contributor

Re: How to count values from two joined qvd-files?

Thanks you for your reaction. It worked!

m_dirksz
New Contributor

Re: How to count values from two joined qvd-files?

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

Community Browser