Return value based on match/intervalmatch? (newbie question)

Thu, 03 Sep 2009 02:43:19 GMT

I am trying to pull an ID value out of a lookup table based on a matching input from another table. Consider the following:


Periods:
LOAD * INLINE [
ID, StartDate, EndDate
1, '1/1/2007', '12/31/2007'
2, '1/1/2008', '12/31/2008'
3, '1/1/2009', '12/31/2009'
];

Dates:
LOAD * INLINE [
Date
'7/31/2007'
'6/30/2007'
'5/1/2008'
'9/14/2009'
];

I want to evaluate the range that each Date from the Dates table falls into within the StartDate & EndDate in the Periods table. I have successfully done this with the intervalmatch function. But, what I really want is to return the ID field after I've done that comparison so I can append the ID field to the Date table. (I've simplified this example - Dates would have many other columns. I want to end up with ID in the Dates table and drop the Date field.

My resulting table would look like this:


Dates:
1
1
2
3

Thanks in advance for your advice.

Jim

Return value based on match/intervalmatch? (newbie question)

johnw — Thu, 03 Sep 2009 06:07:53 GMT

This probably isn't the most efficient approach, but it does the job:

SET DateFormat='M/D/YYYY';

Periods:
LOAD * INLINE [
ID,StartDate,EndDate
1,1/1/2007,12/31/2007
2,1/1/2008,12/31/2008
3,1/1/2009,12/31/2009
];
Dates:
LOAD * INLINE [
Date, Field1, Field2
7/31/2007,A,B
6/30/2007,C,D
5/1/2008 ,E,F
9/14/2009,G,H
];
LEFT JOIN ([Dates])
INTERVALMATCH (Date)
LOAD
StartDate
,EndDate
RESIDENT [Periods]
;
LEFT JOIN ([Dates])
LOAD
StartDate
,EndDate
,ID
RESIDENT [Periods]
;
DROP TABLE [Periods]
;
DROP FIELDS
StartDate
,EndDate
;

topic Return value based on match/intervalmatch? (newbie question) in QlikView

Return value based on match/intervalmatch? (newbie question)

Return value based on match/intervalmatch? (newbie question)