-
Select year in date range
DateRanges:
Load ID, Date#(Begindate,'DD-MM-YYYY') as Begindate, Date#(Enddate,'DD-MM-YYYY') as Enddate inline
[ID, Begindate, Enddate
1, 01-01-2008, 26-5-2010
2, 05-06-2009, 27-5-2016
3, 05-06-2010, 31-12-2011
4, 01-01-2000, 15-08-2020
5, 05-09-2011, 21-10-2012
6, 05-07-2007, 31-11-2008
7, 08-08-2008, 07-08-2012
8, 11-10-2000, 15-03-2002
9, 04-03-2007, 03-04-2008
10, 04-04-2011, 31-12-2012];
// ========== Find lowest and highest date ==========
AllDates:
Load Begindate as TempDate resident DateRanges;
Load Enddate as TempDate resident DateRanges;
MinMaxDate:
Load Min(TempDate) as MinDate, Max(TempDate) as MaxDate resident AllDates;
Let vMinDate = peek('MinDate',-1,'MinMaxDate') -1 ;
Let vMaxDate = peek('MaxDate',-1,'MinMaxDate') ;
Drop Tables AllDates, MinMaxDate ;
// ========== Generate all Dates ==========
Dates:
Load *, Year(Date) as Year;
Load Date($(vMinDate) + recno()) as Date autogenerate $(vMaxDate) - $(vMinDate) ;
// ========== Perform an IntervalMatch ==========
IntervalMatch:
IntervalMatch (Date) Load Begindate, Enddate resident DateRanges;
-
Re: Select year in date range
Thanks a lot Henric for the help. It works great. Could you explain with a little more detail what the code does in each step. So I understand it better.
Thanks a lot!!
-
Re: Select year in date range
The first Load - DateRanges - just loads your data and makes sure that the dates are interpreted as numbers. Make a list box of a date and format it as number (List box properties-Number-Integer) then you'll see.
In AllDates I load all numbers in one field, so I can load the smallest and largest into the subsequent MinMaxDate table, which has only one record.
The two Let statements just load the smallest and largest value into variables. When this is done, I can drop both AllDates and MinMaxDate. I do not need them anymore.
Having these two variables, I can generate a table Dates containing all dates between the two variable values using a Load ... autogenerate.
Finally, the Intervalmatch is the magic: The intervalmatch transformation compares the ranges defined in DateRanges with the individual values in Dates and generates all combinations between the two.
And that's it...
/HIC
-
-
Re: Select year in date range
The problem now is that it generates to much rows 22 million and Qlikview stops responding and gives the "Out of Memory" error.
Is there an other way to solve this or to use the existing dates instead of all the dates in between for example?
-
Re: Select year in date range
Use existing dates instead. Then you do not need the "Load ... autogenerate". And the Intervalmatch should be "IntervalMatch (ExistingDate) Load ..." instead.
/HIC
-
-
Select year in date range
Hi,
Thanks for this.....i was also looking for this one & reached here on a single search........really useful..........
But could you please explain abt the 'ExistingDate'......which date/field is it ? and if i dnt use autogenerate then how the dates will be generated........actually i'm sill learnig its correct usage.
Regards
-
Select year in date range
The intervalmatch is all about comparing one table with date ranges to another table with individual dates. (Dates or other types of numbers). In this case the table with ranges exist and the question is where to find the individual dates.
If you have them in a table, you can load this table and use these dates.
If you don't have the dates in a table, you need to generate them using e.g. a Load autogenerate.
In both cases, both the table with the ranges and the table with the dates need to be loaded before the Intervalmatch is done.
In djibiont's case, I assumed that there was no existing dates table - because it was not mentioned - so I suggested an autogenerate. But there was, and then it makes more sense to use this table than an autogenerate.
/HIC
-
Select year in date range
Thanks you for the reply Sir,
As u've said:
"If you have them in a table, you can load this table and use these dates."
Can i use the following code to get all the dates from my table:
AllDates:
Load
distinct startdate as date resident tablename;
concatenate
Load distinct enddate as date resident tablename;
and then intervalmatch:
interalmatch(date) load startdate,enddate resident tablename;
Will this work ?
Regards
-
Select year in date range
Your "date" field will be incomplete - it will only contain the dates that happen to be the start of end of some range. It will not contain the dates in between. But the intervalmatch will work. So if an incomplete "date" field is OK, then you can use the the above code.
/HIC
-
Select year in date range
Sir,
actually i'm nt able to understand:
" it will only contain the dates that happen to be the start of end of some range. It will not contain the dates in between"
can u explain a bit more.......
no actually i want all the dates & not incomplete dates.........then i think i should remove distinct and then concatenate.
plz throw some light
Regards
-
Select year in date range
If you want all the dates, then the table containing the "date" field must have one record per date. But your suggested solution will only load the dates that already exist in your table that contain the ranges. In other words, your dates table will have just some of the dates.
Ranges table: One record per date range.
Dates table: One record per date.
Intervalmatch table: One record per match between the two previous tables.
Try the script posted above with a Load ... autogenerate.
/HIC
-
Select year in date range
SIr,
I'll have to read and analyse what u're saying at least 4-5 times then i'll understand it........i'm not even half as sharp as you.
for the time being i've just removed distinct & loading all the start and enddates.........isnt it okay?
Regards
-
-
-
-
-
-
-
-
-
-
-
-