I'm trying to generate a master 4 4 5 calendar using our existing Accounting 4 4 5 Calendar table and INTERVALMATCH. The table contains the starting date, ending date, period and year (Example in QVW attached). I'm first loading that table. Using the starting date from the first record and the ending date from the last record, I AUTOGENERATE a TempCalendar. Then I'm trying to align the TempCalendar to the Accounting Calendar table using INTERVALMATCH. Problem I'm having is that each date is aligning with each Calendar record (Example in QVW attached).
I'd appreciate any help I can get.
Regards,
Rich De Rocco
This is my script.
// Load the Fiscal Calendar date range file Contains Starting and Ending Date Ranges for each period FiscalCalTemp: LOAD Year, Period, Makedate('20' & Right (StartDate,2), Left (StartDate,2), Mid (StartDate,4,2)) As StartDate, Num (StartDate) As Start, Makedate('20' & Right (EndDate,2), Left (EndDate,2), Mid (EndDate,4,2)) As EndDate, Num (EndDate) As End, Month FROM Calendar.xls (biff, embedded labels, table is Sheet1$);
//set this variable to define dates to generate LET varMinDate = Num(Peek('StartDate', 0, 'FiscalCal')); LET varMaxDate = Num(Peek('EndDate', -1,'FiscalCal')); LET varToday =num(Today());
//Create all days in the range from varMinDate to varMaxDate TempCalendar: LOAD $(varMinDate)+Iterno()-1 AS Num, Date($(varMinDate)+Iterno()-1) AS TempDate AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1 <= $(varMaxDate); Left Join (FiscalCalTemp) INTERVALMATCH (Num, TempDate) LOAD Start, End RESIDENT FiscalCalTemp; LEFT JOIN (FiscalCalTemp) LOAD * Resident TempCalendar;
Load Distinct Year As YR, Period, StartDate, EndDate, Month As MTH, TempDate RESIDENT FiscalCalTemp;
Drop TABLE FiscalCalTemp; DROP TABLE TempCalendar;
I think you could do it by replacing the current intervalmatch with this, but I can't easily test since I don't have the Excel file:
LEFT JOIN (FiscalCalendar) INTERVALMATCH(Num) LOAD Start, //StartParameter for intervalmatch() End //EndParameter for intervalmatch() RESIDENT FiscalCalendar;
LEFT JOIN (FiscalCalendar) LOAD * RESIDENT TempCalendar;
I've reworked the code and I think I have a solution. However, it generates synthetic keys but I'm not all that certain I understand why sythetic keys in this event should be avoided.
In a December 9,2009 post from Orka entitled 'Forget About IntevalMatch, John Witherspoon reponded:
Our new Period table sits EXACTLY where the synthetic key table sat, and is serving EXACTLY the same role. But since we did everything EXPLICITLY, it's a "real" key instead of a "synthetic" key. Does that matter? Here it does not, or at least I can't think of any down side to leaving the key synthetic. Not all synthetic keys are bad, and I think this particular synthetic key is exactly what is needed. So really, all we've done is written a bunch of script to do explicitly what a much shorter and clearer script would have accomplished.
And I tend to agree that "Not all sysnthetic keys are bad". While I can create "real" keys, in this case where a generated date is unique and doesn't pose the possibility of duplicate data unless someone can point out why this is a bad idea, I'm inclided to go with the attached solution.
I plan on using the TempDate to link to sales transactions to align the sales performance to the Financial information to maintain period integrity.
I hope this can help someone else facing this issue.
On brief glance, I doubt that the synthetic key is causing any problem here. That said, if I understand what's going on here, I'd personally just join all of the tables together because I consider the resulting data structure simpler, even if it denormalizes the information about the periods. So your calendar would then be a list of dates and the associated period and other information. That's how my calendars typically look.
My current understanding of synthetic keys is detailed and discussed in this thread:
As a quick summary, I still think synthetic keys are appropriate IF they exactly mimic a key you would have designed manually, and this appears to be confirmed by QlikTech. But they are often or usually encountered as a result of underlying data model problems, and in that case, the data model problems should be fixed, and often merely removing the synthetic key fixes the data model. So for beginners, it isn't necessarily bad advice to suggest that they always remove synthetic keys, even if in some cases they'd be better off leaving them. There is still an open question about whether it is possible to have a correct data model confusing enough that QlikView finds it difficult to figure out the right key structure, and as a result encounters significant performance and/or memory problems trying to build the correct synthetic keys.
Synthetic keys are not necessarily bad and I might as well let QV do the lifting for me.
As far as bringing the files together, that would be my preference. In the end I would prefer to have a calendar with only dates associated with the period range. I just don't know how to do it without disassociating the match that was achieved by the IntervalMatch and therefore creating duplications.
I think you could do it by replacing the current intervalmatch with this, but I can't easily test since I don't have the Excel file:
LEFT JOIN (FiscalCalendar) INTERVALMATCH(Num) LOAD Start, //StartParameter for intervalmatch() End //EndParameter for intervalmatch() RESIDENT FiscalCalendar;
LEFT JOIN (FiscalCalendar) LOAD * RESIDENT TempCalendar;
