Associating Master Calendar with Fact tables

[iluilyas]

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

[swuehl]

Where do you create your variables varMinDate and varMaxDate?

[iluilyas]

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

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

before MasterCalendar

[swuehl]

Shouldn't you create the variables before TempCalendar table?

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

[svinnakota]

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]

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 · Accepted solution]

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]

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]

Issue resolved Santosh. Thanks for your time though!

[taha_mansoor]

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