3 Replies Latest reply: Aug 30, 2012 11:46 PM by Kiran Rokkam RSS

    Indirect Joins in Load Script

      Hi,

      I have an autogenerated list of dates where I want to tag each date with a value based on a separate set of date ranges (eg. season).  I would really appreciate any suggestions on how to achieve this.  My best attempt so far is:

       

      SET vDate_Key_Min = Today() - 365;

      SET vDate_Key_Max = Today();

       

      CALENDAR:

      LOAD

                Date(IterNo() + Date($(vDate_Key_Min))) AS DATE_KEY,

      AUTOGENERATE 1 WHILE Date(IterNo() + Date($(vDate_Key_Min))) <= Date($(vDate_Key_Max));

       

      SEASON:

      LOAD PERIOD,

          FRDT,

          TODT;

      SQL SELECT PERIOD,

          FRDT,

          TODT

      FROM POSDEMO."RET_PERIODS";

       

      JOIN CALENDAR:

      LOAD PERIOD

      RESIDENT WEEKS WHERE CALENDAR.DATE_KEY >= FRDT AND CALENDAR.DATE_KEY <= TODT;

       

      I know the dot naming convention doesn't work in Qlikview but I thought it might clarify what I'm trying to achieve.

       

      I want to end up with a table that has:

      01/01/2012 | SUMMER

      02/01/2012 | SUMMER

      ...

      01/06/2012 | WINTER

      02/06/2012 | WINTER

      etc.

       

      Thanks

      Luke