Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Michiel_QV_Fan
Specialist
Specialist

Extended interval match does nothing

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?

2 Solutions

Accepted Solutions
jepeerik
Contributor III
Contributor III

Hi Michiel,

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

IntervalMatch multiple key

Sharing knowledge increases your knowledge

View solution in original post

gardenierbi
Creator II
Creator II

LEFT JOIN (Facts)

LOAD

    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.

View solution in original post

9 Replies
Kushal_Chawda

try this

Data:

LOAD *

(Startdate+iterno()-1) as Date

FROM Employees

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

Michiel_QV_Fan
Specialist
Specialist
Author

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.

jepeerik
Contributor III
Contributor III

Hi Michiel,

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

IntervalMatch multiple key

Sharing knowledge increases your knowledge
sunny_talwar

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?

Michiel_QV_Fan
Specialist
Specialist
Author

No Sunny, I can't post a QVW.

sunny_talwar

May be the script before the interval match?

Michiel_QV_Fan
Specialist
Specialist
Author

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

gardenierbi
Creator II
Creator II

LEFT JOIN (Facts)

LOAD

    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.

Michiel_QV_Fan
Specialist
Specialist
Author

I will try this too Sander. Thanks