topic Re: Indirect Joins in Load Script in QlikView

Indirect Joins in Load Script

Thu, 30 Aug 2012 03:24:56 GMT

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

Re: Indirect Joins in Load Script

Thu, 30 Aug 2012 05:00:53 GMT

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.

Re: Indirect Joins in Load Script

Thu, 30 Aug 2012 22:21:35 GMT

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.

Re: Indirect Joins in Load Script

Fri, 31 Aug 2012 03:46:04 GMT

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

Kiran.