topic Help with interval match in QlikView

Help with interval match

Wed, 10 Feb 2010 21:01:49 GMT

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?

Help with interval match

lironbaram — Wed, 10 Feb 2010 21:39:36 GMT

try this example

i should give you regulr preformence

hope its helps

Help with interval match

Wed, 10 Feb 2010 21:50:26 GMT

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

Help with interval match

johnw — Wed, 10 Feb 2010 21:53:39 GMT

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.

Help with interval match

Thu, 11 Feb 2010 02:00:49 GMT

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?

Help with interval match

Sat, 13 Feb 2010 04:40:31 GMT

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

Re: Help with interval match

Thu, 25 Apr 2013 20:12:32 GMT

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!

Re: Help with interval match

Thu, 26 Jun 2014 23:02:52 GMT

THANK YOU JOHN! Huge help for me as well