Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

iluilyas
New 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
MVP
MVP

Re: Associating Master Calendar with Fact tables

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?

10 Replies
MVP
MVP

Re: Associating Master Calendar with Fact tables

Where do you create your variables varMinDate and varMaxDate?

iluilyas
New Contributor III

Re: Associating Master Calendar with Fact tables

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

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

before MasterCalendar

MVP
MVP

Re: Associating Master Calendar with Fact tables

Shouldn't you create the variables before TempCalendar table?

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

svinnakota
Contributor

Re: Associating Master Calendar with Fact tables

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
New Contributor III

Re: Associating Master Calendar with Fact tables

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.

MVP
MVP

Re: Associating Master Calendar with Fact tables

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
New Contributor III

Re: Associating Master Calendar with Fact tables

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
New Contributor III

Re: Associating Master Calendar with Fact tables

Issue resolved Santosh. Thanks for your time though!

taha_mansoor
New Contributor III

Re: Associating Master Calendar with Fact tables

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

Community Browser