Problems with IntervalMatch

[bengan74]

I'm trying to get Interval match to work, but it seems like I'm having some date format issues.

The first table is called "DateTable" and it's a table only containing the field "Date".

In the reload script I have one LOAD * INLINE, and one loop creating a date table (The loop is commented now).

The second table is "Items" and here we find the items, customer and price, and the intevall for different prices ("StartDate" and "EndDate").

The third table is "BridgeTable" were I use IntervalMatch to match the field "Date" with the interval between "StartDate" and "EndDate".

This works as I want it to, but I having a LOAD * INLINE-table isn't the best solution so I've tried to use the same loop as the Date calendar to generate a Date table, but it doesn't work.

Please try in the attached QVW-file.

First filter one of the dates in the List box "Date". You'll now see that "Test table" is working as it should. Only prices that are valid during the date interval is shown.

Then you Edit reload script and comment row 2 to 7, and remove the comment on row 9 to 14 so the loop is active and then execute the script reload.

Now the "Test table" will be blank if you do selections in the list box "Date", and the dates seems to have the same format.

Is there some how understand my explaination AND what I've done wrong?

Thanks in advance

//Johan

[Anonymous · Accepted solution]

How about something like this with the extra date conversions to ensure all your dates are held as true QlikView dates ?

DateTable:

Load

  Date($(vStartDate)+(Iterno()-1),'YYYYMMDD') as Date,

  Iterno() as ID

Autogenerate 1

While Date($(vStartDate)+(Iterno()-1)) <=Date($(vEndDate));

Items:

load

  Item,

  Price,

  CustomerGroup,

  Date( Date# (StartDate,'YYYYMMDD') , 'YYYYMMDD' ) as StartDate ,

  Date( Date# (EndDate,'YYYYMMDD') , 'YYYYMMDD' ) as EndDate

;

LOAD

  *

INLINE [

Item,Price,CustomerGroup,StartDate,EndDate

  3802001,10,9999,20131201,20131231

  3802001,20,9999,20140101,20140131

  3802001,30,9999,20140201,20140228

  3802001,15,19999,20131201,20131231

  3802001,25,19999,20140101,20140131

  3802001,35,19999,20140201,20140228

  3802001,100,29999,20131201,20131231

  3802001,200,29999,20140101,20140131

  3802001,300,29999,20140201,20140228

  3802001,500,39999,20131201,20131231

  3802001,1000,39999,20140101,20140131

  3802001,1500,39999,20140201,20140228

  ];

BridgeTable:

IntervalMatch (Date)

Load distinct StartDate, EndDate Resident Items;

[bengan74]

Damn! Works like a charm! Thank you!!