topic how to link a master calender? in QlikView

how to link a master calender?

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

Hello Community,

I am still very new and inexperienced in dealing with Qlik. But now I've been working a bit, but would now end up with my still modest Latin.

The following: I have two SQL queries from a system. One about the entrances to the company and in a second query the withdrawals from the company.

Here are my queries:

1) Entries: Select cost Center, cost Center Name, Company Name, entry year, entry month, "total entries per month", month Name

2) Withdrawal: Select cost Center, cost Center Name, Company Name, Exit year, exit month, "total Exit per month", month Name

This is my current calender i tried:

Load

floor(num(??) as %calender_year_employee

from ..\..\..\Tools\1QVDPicture\Calender.qvd

At the moment I have created one list for entries and one for exits. Nevertheless, I need one calender for each report (one calender which is linked to the entry year, entry month and the other ones which is linked to the exit year, exit month)

Honestly I would prefer that the people have to select out of one calender and both report-lists will change appropriate.

However, now i dont know what to put in the brackets above (calender) and how to link with my entry year, entry month and exit year, exit month.

Thanks for your help.

Re: how to link a master calender?

OmarBenSalem — Wed, 29 Nov 2017 09:11:01 GMT

Please refer to these:

Canonical Date

Why You sometimes should Load a Master Table several times

Re: how to link a master calender?

YoussefBelloum — Wed, 29 Nov 2017 09:41:12 GMT

Hi,

There is a lot a options here on how to link your calendar table to your fact tables.

for example here, you have two fact tables (entrances ansd withdrawals),

you can for example:

create a date field from the month and the year on each table EXAMPLE ==>(Date(MakeDate(Year, Month, 1), 'MMM-YYYY')
concatenate these two tables (in one fact table)
create a calendar table with autogenerating all the dates from the first date to lastest date present on the fac table

like this you will have on date linked to one entrance date and one withdrawal data simultaneously, and so compare your entrances with your withdrawals with a time axis.

ALL THIS IS ONE APPROACH !

Re: how to link a master calender?

OmarBenSalem — Thu, 30 Nov 2017 19:50:58 GMT

It would be sthing like this:

here's a script you can use to build a test application and understand how to handle different dates:

SET DateFormat='YYYY-M-D';

Table1:

LOAD * INLINE [

ID, Seq, ActivityDate, Value1

1, 1, 2011-5-10,10

1, 2, 2011-5-12,2

2, 1, 2011-5-12,20

3, 1, 2011-6-15,40

];

Table2:

LOAD recno() as ID, * INLINE [

DepartureDate, ArrivalDate, Value2

2011-6-15,2011-6-16,5

2011-6-15,2011-6-17,10

2011-6-16,2011-6-16,20

];

OtherTable:

LOAD recno() as OtherID, * INLINE [

OtherDate, OtherValue

2011-5-12,2

2011-6-15,3

2011-6-17,5

];

Link:

LOAD

,Seq

,ActivityDate as Date

,'Activity' as DateType

RESIDENT Table1

;

CONCATENATE (Link)

LOAD

,DepartureDate as Date

,'Departure' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

,ArrivalDate as Date

,'Arrival' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

OtherID

,OtherDate as Date

,'Other' as DateType

RESIDENT OtherTable

;

Calendar:

LOAD *

,date(monthstart(Date),'MMM YYYY') as Month

;

LOAD date(makedate(2011,5,1)+recno()-1) as Date

AUTOGENERATE 61

;