5 Replies Latest reply: Nov 23, 2017 3:35 AM by Mousa Dirksz RSS

    How to count values from two joined qvd-files?

    Mousa Dirksz

      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!