Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
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

3 Replies
Not applicable
Author

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
Author

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
Author

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

Kiran.