topic Re: Can't get IntervalMatch to work in QlikView

Can't get IntervalMatch to work

Wed, 12 Mar 2014 10:33:13 GMT

Hi,

I've been using IntervalMatch in a QV application, but it creates a lot of synthetic keys. Therfore I'm working on understandig the IntervalMatch function. I started out with the examples from the QV helpfile, and cannot get it right. Clearly I'm missing something. I'd like to know, what I'm doing wrong.

Re: Can't get IntervalMatch to work

Wed, 12 Mar 2014 10:49:34 GMT

I don't know much of it myself, but this might help you learn about it

IntervalMatch

by Henric Cronström

Cheers,

Lucas

Re: Can't get IntervalMatch to work

MK_QSL — Wed, 12 Mar 2014 10:49:47 GMT

Eventlog:

LOAD Time,

Event,

Comment

FROM

(ooxml, embedded labels, table is Sheet1);

Orderlog:

LOAD Start,

End,

Order

FROM

(ooxml, embedded labels, table is Sheet1);

//SQL SELECT * FROM OrderLog;

//SQL SELECT * FROM Eventlog;

IntervalMatch (Time)

Load distinct Start, End resident OrderLog;

Left Join (Eventlog) Load * Resident Orderlog;

Drop Table Orderlog;

Re: Can't get IntervalMatch to work

MarcoWedel — Thu, 13 Mar 2014 00:26:16 GMT

Hi Renskje,

although your solution should work perfectly with the synthetic keys included in your data model, you could get rid of them e.g. like this:

tabEventlog:
LOAD
  Time,
  Event,
  Comment
FROM

(biff, embedded labels, table is [Sheet1$]);

tabOrderlog:
LOAD
  Start,
  End,
  Order
FROM

(biff, embedded labels, table is [Sheet1$]);

tabTempIntervalMatch:
IntervalMatch (Time)
LOAD distinct Start, End Resident tabOrderlog;

Left Join (tabEventlog)
LOAD Distinct
  Time,
  Time(Start, 'hh:mm')&'-'&Time(End, 'hh:mm')as %OrderStartEnd
Resident tabTempIntervalMatch;

DROP Table tabTempIntervalMatch;

Left Join (tabOrderlog)
LOAD Distinct
  Start,
  End,
  Time(Start, 'hh:mm')&'-'&Time(End, 'hh:mm')as %OrderStartEnd
Resident tabOrderlog;

This script turns this data model

into this

no synthetic key tables, simply one additional key field per table

hope this helps

regards

Marco

Re: Can't get IntervalMatch to work

Mon, 17 Mar 2014 12:39:42 GMT

Tried that already, but didn't help

Re: Can't get IntervalMatch to work

Mon, 17 Mar 2014 12:40:08 GMT

Unfortunately, there will still be synthetic keys.

Re: Can't get IntervalMatch to work

Mon, 17 Mar 2014 12:41:33 GMT

Thanks very much! It works, all synthetic keys are removed. Very helpful with all the information on data model and final sheet!

Re: Can't get IntervalMatch to work

MarcoWedel — Mon, 17 Mar 2014 13:17:45 GMT

nice to hear that it works for you.

Did you intentionally mark your own comment as the correct answer? 😉

regards

Marco

Re: Can't get IntervalMatch to work

Tue, 18 Mar 2014 07:25:20 GMT

Haha, just proved myself to be a rookie

Re: Can't get IntervalMatch to work

MarcoWedel — Tue, 18 Mar 2014 07:28:17 GMT