Loading data with a summary

[#1]

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
1	01-01-2014
2	01-01-2014
3	01-01-2014
4	03-01-2014
5	03-01-2014
...	...

Invoices (from SQL Server):

Invoice no.	Invoice Date
1	02-01-2014
2	02-01-2014
3	03-01-2014
4	03-01-2014
5	03-01-2014
...	...

Data load result table I need:

Date	No. of orders	No. of invoices
01-01-2014	3	0
02-01-2014	0	2
03-01-2014	2	3
04-01-2014	0	0

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!

[JonnyPoole · Accepted solution]

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 <>;

[#3]

Thanks Jonathan, that worked like a charm!