Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bernhard_krippl
New Contributor II

how to link a master calender?

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.

Tags (2)
3 Replies
Highlighted
OmarBenSalem
Esteemed Contributor

Re: how to link a master calender?

YoussefBelloum
Esteemed Contributor

Re: how to link a master calender?

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 !

OmarBenSalem
Esteemed Contributor

Re: how to link a master calender?

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

ID

,Seq

,ActivityDate as Date

,'Activity' as DateType

RESIDENT Table1

;

CONCATENATE (Link)

LOAD

ID

,DepartureDate as Date

,'Departure' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

ID

,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

;

Capture.PNG

Capture.PNG

Community Browser