topic Re: Loading data with a summary in Connectivity & Data Prep

Loading data with a summary

Thu, 06 Nov 2014 18:48:47 GMT

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!

Re: Loading data with a summary

JonnyPoole — Fri, 07 Nov 2014 01:11:44 GMT

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

Re: Loading data with a summary

Fri, 07 Nov 2014 16:52:09 GMT

Thanks Jonathan, that worked like a charm!