Can't get IntervalMatch to work

[#1]

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.

[#2]

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

IntervalMatch

by Henric Cronström

Cheers,

Lucas

[MK_QSL]

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;

[MarcoWedel · Accepted solution]

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

[#5]

Tried that already, but didn't help

[#6]

Unfortunately, there will still be synthetic keys.

[#7]

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

[MarcoWedel]

nice to hear that it works for you.

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

regards

Marco

[#9]

Haha, just proved myself to be a rookie

[MarcoWedel]