Create Calendar for 3 Dates in 3 Tables

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following tables:

CUSTOMERS
DOC_NUM	LAST_NAME	FIRST_NAME	BIN_NUM	BIRTH_DATE	CREATE_DATE
1	SMITH	JOHN	557	01/01/1985	01/02/2017
2	BLACK	JACK	855	02/03/1986	02/02/2017
3	ELLIS	SOPHIE	358	04/05/1987	03/03/2017
4	JOHNSON	MAX	125	15/10/1989	04/03/2017
5	DONALD	WILL	456	05/07/1980	01/04/2017

TRANSACTIONS
BIN_NUM	TRANS_DATE	TRANS_TIME	TRANS_NUM	SHOP	TOTAL
557	05/02/2017	15:15	2258	LOC1	558
855	07/02/2017	17:18	3325	LOC1	125
358	05/03/2017	20:05	1225	LOC3	85

AIRLINE_MILES
DOC_NUM	MILES_DATE	MILES_AMT	AIRLINE_NUM	SHOP	BILL_NUM
1	05/02/2017	100	A180	LOC1	850
2	07/02/2017	200	A185	LOC1	855
3	05/03/2017	300	A190	LOC3	900

The thing is I need to create a Master Calendar to handle the following dates from the 3 tables.

- CUSTOMERS: CREATE_DATE

- TRANSACTIONS: TRANS_DATE

- AIRLINE_MILES: MILES_DATE

So that if I filter one Date (Year/Month/Day) I'd like to know how:

- How many Customers were created on that period of time.

- How many Transactions were made and which amount.

- How many Miles where exchanged on that date.

Do you know how could I connect the Data Model so that I could handle dates from all tables?

Thank you!

Re: Create Calendar for 3 Dates in 3 Tables

rahulpawarb — Mon, 03 Apr 2017 12:49:02 GMT

Hello Micro,

Please refer below:

Canonical Date

Hope this will help.

Regards!

Rahul

Re: Create Calendar for 3 Dates in 3 Tables

tunoi — Mon, 03 Apr 2017 13:43:59 GMT

You need to concatenate those 3 tables into one and rename the date fields to have the same name. After that you will be able to build objects that respond to your questions

topic Create Calendar for 3 Dates in 3 Tables in QlikView

Create Calendar for 3 Dates in 3 Tables

Re: Create Calendar for 3 Dates in 3 Tables

Re: Create Calendar for 3 Dates in 3 Tables