Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
I don't know much of it myself, but this might help you learn about it
Cheers,
Lucas
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;
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
Tried that already, but didn't help
Unfortunately, there will still be synthetic keys.
Thanks very much! It works, all synthetic keys are removed. Very helpful with all the information on data model and final sheet!
nice to hear that it works for you.
Did you intentionally mark your own comment as the correct answer? 😉
regards
Marco
Haha, just proved myself to be a rookie