Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Tags (3)
3 Replies
Not applicable

Re: Indirect Joins in Load Script

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.

Not applicable

Re: Indirect Joins in Load Script

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.

Not applicable

Re: Indirect Joins in Load Script

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

Kiran.