Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iluilyas
Contributor III
Contributor III

Re: Associating Master Calendar with Fact tables

Hi Rob, i changed the data structures a bit and not Year has some issue which i cannot solve:

Fact tables:

PurchaseOrders:

LOAD

PURCHS_ORDR_ID,

DATE(PURCHS_ORDR_DATE) AS PO_DATE,....

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY

FROM [$(Fact)\PurchaseOrders.QVD]

(qvd);

PO_Cont_Comp_Key:

LOAD

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY, MATRL_ID, SUPLR_ID

FROM [$(Fact)\PurchaseOrders.QVD]

(qvd);

Contract_Lines:

LOAD

CONTRCT_NO AS CONTRACT_NO,

    DATE(CONTRACT_DATE) AS CONTRACT_DATE, ........

    (CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY

FROM [$(Fact)\Contract_Lines.QVD]

(qvd);

PO_Cont_Comp_Key:

LOAD

(CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY, MATRL_ID, SUPLR_ID

FROM [$(Fact)\Contract_Lines.QVD]

(qvd);  

// Common Datelink table for Calendar

POContractDateLink:

LOAD

PO_CONT_COMP_KEY, PO_DATE AS PO_CONT_DATE, 'PUR' AS PO_CONT_TYPE

RESIDENT PurchaseOrders;

LOAD

PO_CONT_COMP_KEY, CONTRACT_DATE AS PO_CONT_DATE, 'CON' AS PO_CONT_TYPE

RESIDENT Contract_Lines;

Temp:

Load

min(PO_CONT_DATE) as minDate,

max(PO_CONT_DATE) as maxDate

Resident POContractDateLink;

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_CONT_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:

Issue is, connected fields are empty in MasterCalendar. Like, Year, Week, Month are all empty.

But i could see the value for PO_CONT_DATE and both MasterCalendar's PO_CONT_DATE is matching well with POContractDateLink's PO_CONT_DATE. Yet, i dont see Year values. Due to this, i cannot filter the POs by year.


Let me know where is the disconnection happens.

Regards

Ilyas

10 Replies
iluilyas
Contributor III
Contributor III
Author

What I have seen and learnt is all about loading the master calendar from your fact tables.

And the number of fact tables infact complicate the Master calendar.

In my above question, the master calendar that I built using two fact tables works well.

But I do have requests fact table which creates SYN table when i include for common date link.  So, i removed that from the date link.

Lets wait for an input from others.