Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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!
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
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!