Announcements
cancel
Showing results for
Did you mean:
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:

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

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

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

MasterCalendar:

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.

Regards, Ilyas

1 Solution

Accepted Solutions

Take a look at this tutorial.

-Rob

http://qlikviewcookbook.com

8 Replies

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!
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

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!
Contributor III
Author

Hi Kaushik, attached the script

Regards, Ilyas

Take a look at this tutorial.

-Rob

http://qlikviewcookbook.com

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

Specialist

good

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

Tags