topic Select year in date range in QlikView

Select year in date range

Mon, 26 Mar 2012 13:19:23 GMT

Hi all,

I'm fairly new to QV so here's my question.

I'm stuck with this problem.

How do you get records selecting a year where the selected year is present within a start and enddate record.

For example

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

If I would select the year 2010, it should give me records with ID 1,2,3,4 and 7.

How should I do this. I can't get this right with the Master Calendar Solution because I have 2 different date fields.

Or I'm going at it the wrong way......

Thanks!

Select year in date range

hic — Mon, 26 Mar 2012 13:56:43 GMT

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

Wed, 28 Mar 2012 11:36:51 GMT

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

hic — Wed, 28 Mar 2012 12:04:49 GMT

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

Thu, 29 Mar 2012 08:50:58 GMT

Thanks for the explanation!

Re: Select year in date range

Thu, 29 Mar 2012 12:10:08 GMT

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

hic — Thu, 29 Mar 2012 12:43:02 GMT

Use existing dates instead. Then you do not need the "Load ... autogenerate". And the Intervalmatch should be "IntervalMatch (ExistingDate) Load ..." instead.

/HIC

Re: Select year in date range

Thu, 29 Mar 2012 12:52:25 GMT

Works perfect, thanks a lot!

Select year in date range

Thu, 12 Apr 2012 19:29:52 GMT

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

hic — Thu, 12 Apr 2012 20:51:38 GMT

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

Fri, 13 Apr 2012 04:13:36 GMT

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

hic — Fri, 13 Apr 2012 06:27:19 GMT

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

Fri, 13 Apr 2012 06:32:16 GMT

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

hic — Fri, 13 Apr 2012 06:47:32 GMT

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

Fri, 13 Apr 2012 06:52:10 GMT

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