Help with interval match

[#1]

hi,

I have 2 tables I need to link. The first one is a Contracts table which contains the ClientId, the id of the contracted item, start date and end date of the contract. The 2nd one is a sales table, which contains the client id, item id and the invoice date. The idea of linking both is to analyze the sales of items sold under contract.

The first key I need is ClientId&ArticleId, but additionally I need to link the invoice dates to the date range of the contract. I tried defining the key between both and using the simple syntax of IntervalMatch, but I'm getting a loop. So I tried with the extended syntax of IntervalMatch of

DateMatch:

IntervalMatch(InvoiceDate,key_ClientItem) Load StartDate, EndDate, key_ClientItem resident Contracts;

but do to the poor performance after reload, I'm not sure this is the correct approach.

What do you guys suggest?

[lironbaram · Accepted solution]

hi

try this example

i should give you regulr preformence

hope its helps

[#3]

It's exactly what I needed. Thank you very much.

[johnw]

I guess I'm late to the party, but I took the trouble to build an example before the solution was posted, so I'm going to go ahead and post mine too.

So key_ClientItem = ClientId&ArticleId? And that field is on both tables? Looks right to me, then, at least on brief glance. I'd probably join the tables together, though, and not use the concatenated key.

LEFT JOIN ([Sales])
INTERVALMATCH (Date, Client, Item)
LOAD
Start
,End
,Client
,Item
RESIDENT [Contracts]
;
LEFT JOIN ([Sales])
LOAD *
RESIDENT [Contracts]
;
DROP TABLE [Contracts]
;

See attached example.

[#5]

hello again.

these solutions are working great, but now I'm having an issue. If in debug mode I load a million rows (my sales qvd has 400 million rows) everything works great, but at around 100 million the script fails with the dreaded Execution Failed - Execution finished' error. Any clues anyone?

[#6]

Better late than never! Works perfectly and IMHO more elegant than the other solution.

[#7]

Thanks John.   This is a wonderful, straightforward example that solved a huge complicated problem for me.  I wish all examples posted here were as concise and got right to the root issue.  THANKS!

[#8]

THANK YOU JOHN!  Huge help for me as well