Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

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

Re: Loading data with a summary

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

2 Replies
Employee
Employee

Re: Loading data with a summary

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

Re: Loading data with a summary

Thanks Jonathan, that worked like a charm!

Community Browser