Indirect Joins in Load Script

[#1]

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

[#2]

Luke:

I would do this with interval match. eg:

SeasonDefinition:

Load * Inline [

StartDate,EndDate,Season

01/01/2012,03/31/2012,summer

04/01/2012,06/31/2012,rainy

];

Calendar:

as you made it;

IntervalMatch (Date_key) Load StartDate,EndDate Resident SeasonDefinition;

Kiran.

[#3]

Thanks Kiran, that sort of worked, except that it gave me some synthetic keys and left the values as separate tables.  What I was really hoping to achieve was just one new column in the Calendar table.

[#4]

To avoid synthetic fields just drop the unnecessary fields in this table.

Kiran.