topic Re: Multiple Dates in multiple tables - Linking them in Connectivity & Data Prep

Multiple Dates in multiple tables - Linking them

Thu, 01 Dec 2016 10:21:34 GMT

Hi,

I'm new to QlikSense.

I have data in four sheets - "Booking","Posting","Expense","Hostel"

Each of these sheets contain multiple date columns:

Booking Table: [Booked Date],[Arrival Date],[Departure Date],[Void Date],[No Show Date],[Cancelled Date]

Posting Table: [Date]

Expense Table :[Voucher Date]

Hostel Table: [Date]

Can I combine these dates into a single calendar? I want to use date as a filter to calculate various fields from these tables.

Thanks

Re: Multiple Dates in multiple tables - Linking them

sunny_talwar — Thu, 01 Dec 2016 10:30:49 GMT

Try this out:

Canonical Date

Re: Multiple Dates in multiple tables - Linking them

Thu, 01 Dec 2016 11:00:27 GMT

Hey Sunny T,

Can you help me out with this in a bit more detail?

Can you provide me with a script for creating this canonical date? Booking table should be the fine grain I think.

Re: Multiple Dates in multiple tables - Linking them

sunny_talwar — Fri, 02 Dec 2016 10:21:09 GMT

Provide me with the raw data and I might be able to help you out

Re: Multiple Dates in multiple tables - Linking them

Fri, 02 Dec 2016 11:03:41 GMT

Hi Sunny,

Here is the link containing the raw data.

Raw Data - Google Drive

Thanks a ton for the help

Re: Multiple Dates in multiple tables - Linking them

sunny_talwar — Fri, 02 Dec 2016 12:17:40 GMT

Can you just attach it here (I don't have access to Google Drive at my work)

Uploading a Sample

Re: Multiple Dates in multiple tables - Linking them

Fri, 02 Dec 2016 12:26:08 GMT

Sure ,

Here is the attached sample.

Re: Multiple Dates in multiple tables - Linking them

Jeet_007 — Thu, 03 Sep 2020 19:09:49 GMT

Hello Sunny sir,

I am facing the same situation and i got stuck in how to link Canonical Dates with Link Table

So can you please share the script / qvf / qvw for the dataset present . Hope you will share it asap

Regards.

Re: Multiple Dates in multiple tables - Linking them

Jeet_007 — Mon, 07 Sep 2020 13:49:20 GMT

Hello @sunny_talwar

I am facing the same situation and i got stuck in how to link Canonical Dates with Link Table

So can you please share the script / qvf / qvw for the dataset present below .
Hope you will share it asap

Regards.

Re: Multiple Dates in multiple tables - Linking them

kumarravi — Thu, 31 Mar 2022 09:39:03 GMT

Hey ,

(For reference I will use only 2 tables of yours, it is very similar for rest)

You can follow the following steps to do this :

Step 1 : Create Date, month, year column using Date column in each table

Posting Table:

Date([Date]) as Posting_date,

Month(Date([Date])) as Posting_month,

Year(Date([Date])) as Posting_year

Expense Table :

Date([Voucher Date]) as Expense_date,

Month(Date([Voucher Date])) as Expense_month,

Year(Date([Voucher Date])) as Expense_year

Step 2: Create a Link Table

Link_table_1 :

Load Distinct

Posting_date as Date,

Posting_month as Month,

Posting_year as Year

Resident [Posting table];

Concatenate Link_table_1

Load

Expense_date as Date,

Expense_month as Month,

Expense_year as Year

Resident [Expense table];

NoConcatenate

Step 3: Create a new table and drop the older one

LINK_TABLE:

Load

Date,

Month,

Year

Resident Link_table_1;

Drop Link_table_1;

Re: Multiple Dates in multiple tables - Linking them

Mahamed_Qlik — Sun, 30 Oct 2022 06:51:50 GMT

HI Kumar

I have tested the steps you have given but it is increasing the number of records for each date you are concatenating
any other solution can we have on this?