Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Can't get IntervalMatch to work

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.

Tags (1)
1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: Can't get IntervalMatch to work

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

QlikCommunity_Thread_110368_Pic1.JPG.jpg

into this

QlikCommunity_Thread_110368_Pic2.JPG.jpg

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

QlikCommunity_Thread_110368_Pic3.JPG.jpg

QlikCommunity_Thread_110368_Pic4.JPG.jpg

hope this helps

regards

Marco

9 Replies
Not applicable

Re: Can't get IntervalMatch to work

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
Not applicable

Re: Can't get IntervalMatch to work

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
Not applicable

Re: Can't get IntervalMatch to work

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

QlikCommunity_Thread_110368_Pic1.JPG.jpg

into this

QlikCommunity_Thread_110368_Pic2.JPG.jpg

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

QlikCommunity_Thread_110368_Pic3.JPG.jpg

QlikCommunity_Thread_110368_Pic4.JPG.jpg

hope this helps

regards

Marco

Not applicable

Re: Can't get IntervalMatch to work

Tried that already, but didn't help

Not applicable

Re: Can't get IntervalMatch to work

Unfortunately, there will still be synthetic keys.

Not applicable

Re: Can't get IntervalMatch to work

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

MarcoWedel
Not applicable

Re: Can't get IntervalMatch to work

nice to hear that it works for you.

Did you intentionally mark your own comment as the correct answer?   ;-)

regards

Marco

Not applicable

Re: Can't get IntervalMatch to work

Haha, just proved myself to be a rookie

MarcoWedel
Not applicable

Re: Can't get IntervalMatch to work