topic Nonequijoin in Load in QlikView

Nonequijoin in Load

matthewjbryant — Wed, 25 Mar 2015 09:54:46 GMT

I currently have two tables in my QVW: one is a list of dates, and the other is a range of dates with period numbers.

Date
01/02/2011
02/02/2011
03/02/2011
04/02/2011

Date From	Date To	Period
01/02/2011	01/02/2011	1
02/02/2011	03/02/2011	2
04/02/2011	04/02/2011	3

(Dates are in DD/MM/YYYY format)

In SQL I could use a nonequijoin to link these table, but I don't believe this is possible in Qlikview. How can I obtain the following table?

Date	Period
01/02/2011	1
02/02/2011	2
03/02/2011	2
04/02/2011	3

Re: Nonequijoin in Load

morganaaron — Wed, 25 Mar 2015 10:59:20 GMT

Hi Matthew,

This is done using an IntervalMatch load (you can read more about it here: http://community.qlik.com/docs/DOC-4310)

The premise is to match up your dates to your 'intervals' as your date to/date from:

Intervals:
IntervalMatch (Date)
Load
[Date From],
[Date To]
Resident Periods;

You can then create the table you're after by simply creating a tablebox with Date and Period in. As a warning, it will create a synthetic join in your model, but this is normal and expected.

Aaron

Re: Nonequijoin in Load

matthewjbryant — Wed, 25 Mar 2015 11:18:32 GMT

Thanks Aaron,

This seems to do the job pretty well, but I think I've stumbled upon a solution that's better for me inLoops in the Script. If I load the table of periods (in my case this is from an Oracle database) I can then loop through with the following script:

Dates:

Load

Period,

Date( DateFrom + IterNo() - 1 ) as Date

Resident Periods

While IterNo() <= DateTo - DateFrom + 1;

Your solution may be better in other situations so I'll hold on to that. Thanks!