Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

matthewjbryant
Not applicable

Nonequijoin in Load

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 FromDate ToPeriod
01/02/201101/02/20111
02/02/201103/02/20112
04/02/201104/02/20113

(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?

DatePeriod
01/02/20111
02/02/20112
03/02/20112
04/02/20113
1 Solution

Accepted Solutions
matthewjbryant
Not applicable

Re: Nonequijoin in Load

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!

2 Replies
morganaaron
Not applicable

Re: Nonequijoin in Load

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

matthewjbryant
Not applicable

Re: Nonequijoin in Load

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!