Joining a local table and a table from SQL to create a Fiscal Calendar

[#1]

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:

FiscalPer:

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:

'2010', '1', '1/1/2010', 29/1/2010'
'2010', '2', '30/1/2010', 27/2/2010'

So I now create a Qlikview Calendar which includes the following information:

FiscalCalendar:

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?

Everything I have tried to date has failed.

[zhadrakas]

Hello Jamie,

try this script:

//Your Source Data:
FiscalPer:
LOAD * INLINE [
FiscalYear, FiscalPeriod, StartDate, EndDate
2010, 1, 01/01/2010, 29/01/2010
2010, 2, 30/01/2010, 27/02/2010
2010, 3, 28/02/2010, 30/03/2010
];

//loop through every period
For i = 0 to NoOfRows('FiscalPer')-1
LET vStartDate = peek('StartDate', $(i), 'FiscalPer');
LET vEndDate = peek('EndDate', $(i), 'FiscalPer');

FiscalCalendar:
Load
TempDate as Date,
Dual(WeekDay(MonthEnd(TempDate)), Mod(3+WeekDay(MonthEnd(TempDate)),7)) as WeekDayMonthEnd,
Dual(WeekDay(MonthStart(TempDate)), Mod(2+WeekDay(MonthStart(TempDate)),7)) as WeekDayMonthStart,
WeekDay(TempDate) as WeekDay,
Month(TempDate) as CalendarMonth,
Year(TempDate) as CalendarYear
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
'$(vStartDate)'-1 as mindate,
'$(vEndDate)' as maxdate
AUTOGENERATE 1;

next i

//cleanup
drop table FiscalPer;
LET i =;
LET vStartDate =;
LET vEndDate =;

[Gysbert_Wassenaar]

Try this:

FiscalPer:

LOAD

     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]

FROM

     source_table

WHILE

     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.