topic Re: Calendar join in QlikView

Calendar join

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

Hi ALl,

I have a table called transcational table and has "Declared_Date " , "Arrive Date" and "Actaul date" and i see that there is script written below to this table .

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Date,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Declared_Date ) as MinDate,
max(Declared_Date ) as MaxDate

Resident Transactional;

But in the table viewer this table is not connected to the Transactional table .
How do i insert "Declared_Date" within this calender so that there will be mapping between Calendar and Transaction table .

Thanks in Advance.

Re: Calendar join

jonathandienst — Mon, 06 Jul 2020 07:28:24 GMT

Replace the first line with:

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Declared_Date,

Re: Calendar join

jonathandienst — Mon, 06 Jul 2020 07:32:52 GMT

If you want to link the calendar to all three dates, these posts will help:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

https://community.qlik.com/t5/QlikView-Documents/Linking-to-two-or-more-dates/ta-p/1495322

Re: Calendar join

smilingjohn — Mon, 06 Jul 2020 08:44:43 GMT

Thanks @jonathandienst

I dud as per your suggestion but the output result is not working .

Can you please explain me what exactly this script is doing ?

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Date,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,
Date(MinDate + IterNo() -1,'YY') & '_' & Num(Week(MinDate + IterNo() -1),'00') as Week

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Declared_Date ) as MinDate,
max(Declared_Date ) as MaxDate

Resident Transactional;

Or is there any other way to handle this ?

Thanks

Re: Calendar join

Kushal_Chawda — Mon, 06 Jul 2020 10:56:22 GMT

Change the script as highlighted by @jonathandienst

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Declared_Date,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,
Date(MinDate + IterNo() -1,'YY') & '_' & Num(Week(MinDate + IterNo() -1),'00') as Week

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Declared_Date ) as MinDate,
max(Declared_Date ) as MaxDate

Resident Transactional;