Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have one fact table (I concatenated 3 tables of data, because there is no (actual) Pkey, but wanted to simplify my data model, and eliminate Syntehtic keys).
My fact table has some fields that will align, i.e. Date Created, some other dates, etc. It seems that for the most part my fact table has produced desirable results ( i did most of my calculations with the tables separated, I have just recently tried to clean everything up).
During this process I have tried to create a Master Calendar.... It seems that it is functioning properly with two of the tables that were concatenated, but not with one of them, even though the master calendar is created off of the same field. has anyone else had this issues?
See picture below:
It's return NULL values for my table that contains a Pkey, but the other two, the two variables following Pkey that say Control Number, each have a Month, year, quarter, etc, linking them based on Date Created. I'm confused because Date Created is what I used to create the master calendar, so how can it be working for some data, (from two different tables) , but not the other
ex code:
Final_Data_Model:
LOAD
DATE([DateCreated]) AS [Date Created],
FROM $(vQVDPath)ABCData.qvd (QVD);
CONCATENATE
LOAD
DATE([Date Created]) AS [Date Created],
FROM $(vQVDPath)DEFData.qvd (QVD);
CONCATENATE
LOAD
DATE([Date Sent]) AS [Date Created],
FROM $(vQVDPath)GHIData.qvd (QVD);
| IF NoOfRows('Final_Data_Model') > 0 THEN |
STORE Final_Data_Model INTO $(vQVDPath)Final_Data_Model.qvd (QVD);
END IF ;
////// MASTER CALENDAR LOAD
QuartersMap:
MAPPING LOAD
ROWNO() AS Month,
'Q' & CEIL (ROWNO()/3) AS Quarter
AUTOGENERATE (12);
Temp:
LOAD
MIN([Date Created]) AS minDate,
MAX([Date Created]) AS maxDate
RESIDENT Final_Data_Model;
LET varMinDate = PEEK('minDate', 0, 'Temp');
LET varMaxDate = PEEK('maxDate', 0, 'Temp');
//DROP TABLE Temp;
TempCalendar:
LOAD
$(varMinDate) + ITERNO()-1 AS Num,
DATE($(varMinDate) + ITERNO() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate) + ITERNO() -1 <= $(varMaxDate);
TempCal:
LOAD
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
MasterCalendar:
Load
TempDate AS [Date Created],
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) As Day,
YEARTODATE(TempDate)*-1 AS CurYTDFlag,
YEARTODATE(TempDate,-1)*-1 AS LastYTDFlag,
INYEAR(TempDate, MONTHSTART($(varMaxDate)),-1) AS RC12,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
APPLYMAP('QuartersMap', MONTH(TempDate), NULL()) AS Quarter,
WEEK(WEEKSTART(TempDate)) & '-' & WEEKYEAR(TempDate) AS WeekYear,
WEEKDAY(TempDate) AS WeekDay
RESIDENT TempCal
ORDER BY TempDate ASC;
//DROP TABLE TempCal;
STORE MasterCalendar INTO $(vQVDPath)MasterCalendar.qvd (QVD);
Accepted Solutions
just change the format of "date created" to number
and post the result the following table again
I think I've found the problem:
I took the DATE format off of all of my [Date Created] fields. For the two tables that work, the unformatted date is a WHOLE number, and the dates that aren't working contain decimals. I believe it to be important to not format my date that contains decimals with FLOOR or CEIL because I would like to maintain the hours, minutes, seconds, for grouping purposes..... Do Master Calendar's not work with dates formatted in this manner?
