Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iluilyas
Contributor III
Contributor III

Associating Master Calendar with Fact tables

Hi

I have below fact tables:

1. PurchaseOrders (Date Field: DATE(PURCHS_ORDR_DATE) AS PO_DATE)

2. Contract_Lines (Date Field: DATE(CONTRACT_DATE) AS CONTRACT_DATE)

Below is my calendar:

Temp:

Load min(PO_DATE) as minDate, max(PO_DATE) as maxDate

Resident PurchaseOrders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate As PO_DATE,

week(TempDate) As Week,

year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & weekyear(TempDate) as WeekYear,

weekday(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Issue:

I'm showing year filter in filter pane and listing the POs and contracts in table with the year, PO/contract number and total line items.

The issue is, when i select a year from the filter, its filtering only Purchase Orders and not contracts.

What you suggest i should be doing as a standard practice when it comes to year/date filters like this scenario where there are multiple tables involved.

Your help is much appreciated.

Regards, Ilyas

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Take a look at this tutorial.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

Download the Qlik Sense example by clicking the "S" icon.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

Check this link for more help.

How to use - Master-Calendar and Date-Values

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
iluilyas
Contributor III
Contributor III
Author

Dear Kaushik

I went through the link and found a similar case of mine where they would like to tie up two fact tables to a single calendar.

They are suggesting to use linked dimension for calendar master.

But due to the lack of clear example, i have difficulty further probing this.

Would you be able to help further with my example?

Regards, Ilyas

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

For that it would be great if you share the snapshot of the data model (LIst of tables and Fields).

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
iluilyas
Contributor III
Contributor III
Author

Hi Kaushik, attached the script

Regards, Ilyas

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Take a look at this tutorial.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

Download the Qlik Sense example by clicking the "S" icon.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

iluilyas
Contributor III
Contributor III
Author

Thanks Rob. Well understood the concept of Date Link tables and implemented.

Hi Kaushik - got it worked and thanks for your help as well.

Regards, Ilyas

arvind1494
Specialist
Specialist

good

kev6brown
Partner - Creator
Partner - Creator

HI, excellent post and I've tried to incorate it in my script however I'm getting "Semantic error", why ?

 

CALL CalendarFromField('Date1', 'CommonCalendar','');
CALL CalendarFromField('EpisodeDate', 'EpisodeCalendar', 'Episode '); 
CALL CalendarFromField('FoodDate', 'FoodCalendar', 'Food '); 
CALL CalendarFromField('PhysioDate', 'PhysioCalendar', 'Physio '); 
 

Kev