Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewjbryant
Creator II
Creator II

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
Creator II
Creator II
Author

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!

View solution in original post

2 Replies
morganaaron
Specialist
Specialist

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
Creator II
Creator II
Author

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!