Joining a local table and a table from SQL to create a Fiscal Calendar
Hello, I'm hoping someone can offer some assistance to this issue I'm having as I've come up against a wall.
My goal is to create a Fiscal Calendar. I previously had an excellent Fiscal Calendar created to suit our needs based on the fact that each Fiscal Period ended on the last Friday of each month. Fine.
Now I'm told that the business has decided to change some of the dates in this fiscal calendar to suit business needs, and they no longer fit a set pattern. They are however set up in our business system so I intend to create a fiscal calendar in Qlikview using data from a table in my Business System.
The table from my business system is as follows:
FiscalYear as [Fiscal Year], FiscalPeriod as [Fiscal Period], StartDate as [Fiscal Period Start Date], EndDate as [Fiscal Period End Date];
The problem I have is that this basically only shows the following info in the table:
So I now create a Qlikview Calendar which includes the following information:
Load Date(Date,'DD/MM/YYYY') as %Key_Calendar_Join_Date, Dual(WeekDay(MonthEnd(Date)), Mod(3+WeekDay(MonthEnd(Date)),7)) as WeekDayMonthEnd, Dual(WeekDay(MonthStart(Date)), Mod(2+WeekDay(MonthStart(Date)),7)) as WeekDayMonthStart, WeekDay(Date) as WeekDay, Month(Date) as CalendarMonth, Year(Date) as CalendarYear, *; Load Date(RecNo()-1+MakeDate(2010)) as Date Autogenerate 10000;
I now have my two tables, but I want to join them, and this is where I have the issue. I need to have a table which includes all of the dates in FiscalCalendar, but also joins the information from FiscalPer to that, so the new table also includes the Fiscal Period for each date, as well as the start and end date of that fiscal period. Can anyone please tell me how I do that?
FiscalYear as [Fiscal Year], FiscalPeriod as [Fiscal Period], Date(StartDate + IterNo() -1,'DD/MM/YYYY') as %Key_Calendar_Join_Date,
StartDate as [Fiscal Period Start Date], EndDate as [Fiscal Period End Date]
StartDate + IterNo() -1 <= EndDate
You can then choose to leave the tables as they are since they will be associated correctly on the %Key_Calendar_Join_Date field. Or you could add additional fields in the FiscalPer table with a preceding load not create the FiscalCalendar table.