2 Replies Latest reply: Nov 8, 2016 5:05 AM by Gysbert Wassenaar RSS

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

    Jamie Maguire

      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.

        • Re: Joining a local table and a table from SQL to create a Fiscal Calendar
          Tim Driller

          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 =;

          • Re: Joining a local table and a table from SQL to create a Fiscal Calendar
            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.