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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

The dates from your fact tables, do they in fact hold timestamp values?

Date() function will only reformat the values, but will not strip off the time part.

If so, try using

DATE(FLOOR(PURCHS_ORDR_DATE)) AS PO_DATE,..


etc.


Why don’t my dates work?

View solution in original post

10 Replies
swuehl
MVP
MVP

Where do you create your variables varMinDate and varMaxDate?

iluilyas
Contributor III
Contributor III
Author

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

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

before MasterCalendar

swuehl
MVP
MVP

Shouldn't you create the variables before TempCalendar table?

And have you checked that the variables hold correct numeric values?

svinnakota
Creator
Creator

Hi IIyas,

Could you share the file with us so that we can get the better picture of the issue. Atleast the screenshot of the datamodel.

Santosh.

iluilyas
Contributor III
Contributor III
Author

Yes, sorry, before TempCalendar and yes, they hold values.

Even the MasterCalendar has all values properly along with PO_CONT_DATE.


Even the PO_CONT_DATE in PurchaseOrders table holds the same date value that the one in MasterCalendar. But Year column is having no value for the PO lines and it has only for Mastercalendar dates where there are no PO for the iterated date.

swuehl
MVP
MVP

The dates from your fact tables, do they in fact hold timestamp values?

Date() function will only reformat the values, but will not strip off the time part.

If so, try using

DATE(FLOOR(PURCHS_ORDR_DATE)) AS PO_DATE,..


etc.


Why don’t my dates work?

iluilyas
Contributor III
Contributor III
Author

That was the issue. I'm now taking PO date from a different field than earlier and it has time part.

Genius thought. Thanks Stefan!

Regards, Ilyas

iluilyas
Contributor III
Contributor III
Author

Issue resolved Santosh. Thanks for your time though!

taha_mansoor
Creator
Creator

Hi,

Could you please let me know the best practice on the place where the master calendar should be developed, I mean, what stage we should write our master calendar script, at Transformation layer(saving it into QVD and then load that Master Calendar QVD on presentation layer)  or at Presentation layer ?

Thanks

Taha