2 Replies Latest reply: Nov 7, 2014 11:52 AM by Luis Seabra Coelho RSS

    Loading data with a summary

      Hi,

       

      I'm trying out Qlik Sense and I'm stuck with a simple problem. I have a series of dates in a table that I build on the load script and I want to include the number of occurrences of each of these dates in 2 tables I'm getting from SQL Server:

       

      Dates (built during the load):

      Date
      01-01-2014
      02-01-2014
      03-01-2014
      04-01-2014
      ......

       

      Orders (from SQL Server):

      Order no.Order Date
      101-01-2014
      201-01-2014
      301-01-2014
      403-01-2014
      503-01-2014
      ......

       

      Invoices (from SQL Server):

      Invoice no.Invoice Date
      102-01-2014
      202-01-2014
      303-01-2014
      403-01-2014
      503-01-2014
      ......

       

      Data load result table I need:

      DateNo. of ordersNo. of invoices
      01-01-201430
      02-01-201402
      03-01-201423
      04-01-201400

       

      I can set up this table on a new sheet, but I wanted to build some stuff on it so it would be much easier for me if the table was available in this format right after the load.

      Is there a way to do this?

       

      Thanks in advance!

        • Re: Loading data with a summary
          Jonathan Poole

          To do this, add an extra field in each load so that Order Date can also be referenced as [Date]. Do this for [Invoice Date] as well. Then Qlik will link the tables on Date. THen you can use Date in a table and count(distinct [Order no.]) as one measure and count(distinct [Invoice No.]) as your 2nd measure.  It may work without distinct , but if the numbers aren't right edit the measure definition in the chart properties / measures on the right side of the screen by clicking the f(x) button.  Let me know if you need to see a sample

           

          Orders:

          Load

               [Order no.],

               [Order Date],

               [Order Date] as [Date]

          SQL select <>;

           

          Load

               [Invoice no.],

               [Invoice Date],

               [Invoice Date] as [Date]

          SQL select <>;