Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.
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)
Thanks you for your reaction. It worked!
I don't really understand this quite yet. Thanks for helping me out though.