Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bengan74
Contributor III
Contributor III

Problems with IntervalMatch

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

1 Solution

Accepted Solutions
bill_markham

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;

View solution in original post

2 Replies
bill_markham

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;

View solution in original post

bengan74
Contributor III
Contributor III
Author

Damn! Works like a charm! Thank you!!