9 Replies Latest reply: May 31, 2016 5:29 AM by Jörgen Peerik

# Extended interval match does nothing

I'm creating an interval match in extended mode:

Bridgetable:

//left join (Facts)

IntervalMatch (Datum, USER)

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

try this

Data:

(Startdate+iterno()-1) as Date

FROM Employees

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

• ###### Re: Extended interval match does nothing

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

LEFT JOIN (Facts)

Startdate + ITERNO() - 1 AS Date,

USER,

<Other fields>

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

I will try this too Sander. Thanks

• ###### Re: Extended interval match does nothing

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

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

No Sunny, I can't post a QVW.

• ###### Re: Extended interval match does nothing

May be the script before the interval match?

• ###### Re: Extended interval match does nothing

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