Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 <>;
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 <>;
Thanks Jonathan, that worked like a charm!