Non-standard joins in QlikView?

Lately we have been testing QlikView in the office. The first impression is good: it has an attractive interface and performs very fast. We want to use it as a database frontend for our customers. We are also trying to determine whether it can take over parts of our relat开发者_StackOverflow社区ional database structure. However, we are in doubt whether its database functions are advanced enough to be more than an attractive frontend.

Specifically, we run into the following problem. The equivalent of normal JOIN (equijoin) operations can be done in QlikView simply by setting equal field names across tables - those fields will then be linked. However, one of our traditional SQL JOIN operations uses a "BETWEEN" query to find out whether a date is in a certain range and join the data on that.

Is it possible to specify such a "non-equijoin" relationship between tables in QlikView? Or is this an inherent limitation to the so-called "associative database" structure?

Marcus' answer is correct. The way to do this is to use IntervalMatch. You can have the two tables as they are and add a "between" relationship between, using IntervalMatch. You can't add relationships after the load script has run.

First you'll have to load the table that has the date range (sql queries omitted). Let's say:

Ranges:
LOAD
    rangeID,
    validfrom, // date
    validto,   // date
    commonkey, // common key for the two tables
    price;     // the data that's needed as a result of the linking

Second, you load another table with the date

Data:
LOAD
    column1,
    column2,
    date,
    commonkey;

Next you will have to use the IntervalMatch. This is one way to do it:

Left Join (Data)
IntervalMatch(date, commonkey)
LOAD
    validfrom,
    validto,
    commonkey
Resident Ranges;

Now you have the link between the two tables. You can delete the resulting synthetic key by adding this:

Left Join (Data)
LOAD
    validfrom,
    validto,
    commonkey,
    rangeID
Resident Ranges;

DROP Fields validfrom, validto FROM Data;

Now the tables are linked by using the rangeID key. If the tables don't have some key in common, like a category id or something, (ie. just the dates need to be matched), you can just ignore the commonkey in the example above. I just wanted to include it in the example because I needed it in my own case and hopefully it will help someone with a similar issue.

You can find this in the Qlikview help labeled "IntervalMatch (extended)". The Qlikview cookbook (fillrowsintervalmatch.qvw) also helped me with this issue.

Sure can - I think what you want it the IntervalMatch function.