Problem with IntervalMatch()

[#1]

Hi!

I have a problmen with doing an intervalmatch. I want to join "date_id" into the table "Main" but I don't get it to work. Do anyone have any idéas what the problem could be? I would be very thankful!!

Main:

LOAD

           date(transaction_date) as transaction_date,

     class & '|' & country as price_key_1

FROM

[..\data\x.QVD]

(qvd);

left join (Main)

LOAD

     system_id,

     price_type & '|' & country as price_key_1   

FROM

[..\data\y.QVD]

(qvd);

Left join (Main)

IntervalMatch (transaction_date, system_id)

LOAD

     date(valid_from) as valid_from,

     if(isnull(valid_to), date('9999-12-31'), date(valid_to)) as valid_to,

     system_id,

     date_id

FROM

[..\data\y.QVD]

(qvd);

[swuehl · Accepted solution]

As far as I remember, the intervalmatch load should only load the two columns defining the numerical interval and (in the extended version) the key fields that you also state in the parantheses after IntervalMatch (system_id in your case).

So let's stick to the sample in the Help and first load/create the two tables you want to link:

Main:

LOAD

           date(transaction_date) as transaction_date,

     class & '|' & country as price_key_1

FROM

[..\data\x.QVD]

(qvd);

left join (Main)

LOAD

     system_id,

     price_type & '|' & country as price_key_1   

FROM

[..\data\y.QVD]

(qvd);

Range:

LOAD

     date(valid_from) as valid_from,

     if(isnull(valid_to), date('9999-12-31'), date(valid_to)) as valid_to,

     system_id,

     date_id

FROM

[..\data\y.QVD]

(qvd);

join IntervalMatch ( transaction_date, system_id) LOAD valid_from, valid_to, system_id resident Range;

Hope this helps,

Stefan

[#3]

Thanks!!