topic Re: Extended interval match does nothing in QlikView

Extended interval match does nothing

Michiel_QV_Fan — Tue, 31 May 2016 09:12:40 GMT

I'm creating an interval match in extended mode:

Bridgetable:

//left join (Facts)

IntervalMatch (Datum, USER)

LOAD

Startdate,

Enddate,

USER

Resident Employees;

I do not get anything from this match. If I change the match to a normal Intervalmatch the intervals and bridgetable are created therefore I'm sure the date formats are ok.

User is in both tables an Integer.

Reviewing the log file I see that the Bridgetable is called in the script but nothing is selected.

What I'm I missing?

Re: Extended interval match does nothing

Kushal_Chawda — Tue, 31 May 2016 09:20:51 GMT

try this

Data:

LOAD *

(Startdate+iterno()-1) as Date

FROM Employees

while Startdate+iterno()-1<=Enddate;

Re: Extended interval match does nothing

Michiel_QV_Fan — Tue, 31 May 2016 09:26:58 GMT

How could I use this? The data is allready loaded so a date range doesn't need to be created in Facts. Start and end dates for the interval are fixed dates in my table.

Re: Extended interval match does nothing

jepeerik — Tue, 31 May 2016 09:29:48 GMT

Hi Michiel,

Maybe you could look at this thread. I think that's the answer you're looking for.

IntervalMatch multiple key

Re: Extended interval match does nothing

sunny_talwar — Tue, 31 May 2016 09:38:11 GMT

I don't see anything wrong with the syntax here, do you have a sample you can share where it isn't working for you?

Re: Extended interval match does nothing

Michiel_QV_Fan — Tue, 31 May 2016 09:39:05 GMT

No Sunny, I can't post a QVW.

Re: Extended interval match does nothing

sunny_talwar — Tue, 31 May 2016 09:40:48 GMT

May be the script before the interval match?

Re: Extended interval match does nothing

Michiel_QV_Fan — Tue, 31 May 2016 09:43:13 GMT

I was pointed out an other post and that seems to be working. Rename the fields from the additional key to the same name.

Testing that as we speak

Re: Extended interval match does nothing

gardenierbi — Tue, 31 May 2016 09:45:16 GMT

LEFT JOIN (Facts)

LOAD

Startdate + ITERNO() - 1 AS Date,

USER,

FROM Employees

WHILE Startdate + ITERNO() - 1 <= Enddate;

If you use this piece of code then you doesn't need the interval match anymore. While 'Startdate + ITERNO() - 1 <= Enddate' is TRUE a new row is generated and ITERNO() is the row counter. So first iteration ITERNO() is 1. Therefore -1 after ITERNO() to correct this offset. First iteration is Startdate + 1 - 1 equals Startdate, second iteration is Startdate + 2 - 1, equals Startdate + 1. Etc.

Re: Extended interval match does nothing

Michiel_QV_Fan — Tue, 31 May 2016 09:48:04 GMT

I will try this too Sander. Thanks