Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

View solution in original post

2 Replies
JonnyPoole
Employee
Employee

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

Not applicable
Author

Thanks Jonathan, that worked like a charm!