Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Interval match and syntethic table

Hallo,

I hope that you guys can help me or at least give a hint.

I was about to create the 'bridge table' with the help of IntervalMatch:

events:

LOAD * INLINE [

    event, eventdate

    a, 12.03.2014

    b, 12.03.2015

];

Interval:

LOAD * INLINE [

    intervalId, begin, end, preis

    1, 01.01.2013, 01.05.2014, 65

    2, 01.01.2013, 01.05.2014, 55

    3, 02.05.2014, 31.12.2015, 75

];

bridge:

IntervalMatch (eventdate)

load begin, end

Resident Interval;

Nevertheless I constantly get the synthetic table.

I have found other discussion where the similar example is shown but without synth table...

here is the data model:

export.png

Thank You in advance!

1 Solution

Accepted Solutions
darkhorse
Valued Contributor

Re: Interval match and syntethic table

Hello,

Synthetic table is recommended in interval match scenarios. At least I recommend through my experience Best possible performance because if you join it into the bridge table (which you can) it multiplies the table and creates a huge one, slowing the performance eventually. As long as the result is correct, this model is best practice.

You can try joining it; you can see yourself. Just write "join" before the bridge table.

BR

Serhan

6 Replies
darkhorse
Valued Contributor

Re: Interval match and syntethic table

Hello,

Synthetic table is recommended in interval match scenarios. At least I recommend through my experience Best possible performance because if you join it into the bridge table (which you can) it multiplies the table and creates a huge one, slowing the performance eventually. As long as the result is correct, this model is best practice.

You can try joining it; you can see yourself. Just write "join" before the bridge table.

BR

Serhan

jfkinspari
Contributor III

Re: Interval match and syntethic table

Include a left join before intervalmatch to get rid of the synthetic dimension.

bridge:

LEFT JOIN (Interval)

IntervalMatch (eventdate)

load begin, end

Resident Interval;

datanibbler
Esteemed Contributor

Re: Interval match and syntethic table

Hi Ira,

IntervalMatch is a two-step process in fact:

- First you use the function to join the intervals (start_date and end_date) to the distinct event_dates;

- Then you can use those two fields (plus a key_field, optionally) to join in the price_data from the intervals_table.

=> The synthetic key after the first step is normal (you haven't yet joined) (think about it, the tables have two fields which are named the
      same and  QlikView automatically links those, so of course you'd get a synthetic key)

Serhan is completely right. Write a JOIN (or I would recommend adding the name of the table you want that to be joined to in brackets like >> JOIN (events) << before the IntervalMatch() to join that to the events_table and the synthetic key will be gone.

=> Then you can, in a second step, join the price_data from the intervals_table to that.

HTH

darkhorse
Valued Contributor

Re: Interval match and syntethic table

Hi Gurus,

Not only that, I claim it would work better with syn table

Please note that and tell me your own experience I claim syn table is life-saver in many real-life scenarios with proper data.

BR

Serhan

jfkinspari
Contributor III

Re: Interval match and syntethic table

I can understand your argument.

I have not made my own experience with huge interval tables, where it could affect performance.

In this case the keyfield is data, so it will probably newer get huge. In this case a clean data model might be preferable

Not applicable

Re: Interval match and syntethic table

Thank you for your answers But I have one more question... How can one avoid synthetic table, when slowly changing dimension is a case? In the example above appears synthetic table...

Thank you in advance

events:

LOAD * INLINE [

    event, eventdate, intervalId

    a, 12.03.2014, 1

    b, 12.03.2015, 3

];

Interval:

LOAD * INLINE [

    intervalId, begin, end, preis

    1, 01.01.2013, 01.05.2014, 65

    2, 01.01.2016, 01.05.2016, 55

    3, 02.05.2014, 31.12.2015, 75

    4, 02.05.2014, 31.12.2015, 85

];

bridge:

right join

IntervalMatch (eventdate, intervalId)

load begin, end, intervalId

Resident Interval;

Community Browser