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
