Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Select year in date range

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!

1 Solution

Accepted Solutions
Employee
Employee

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;

14 Replies
Employee
Employee

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;

Not applicable

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!!

Employee
Employee

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

Not applicable

Re: Select year in date range

Thanks for the explanation!

Not applicable

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?

Employee
Employee

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

Not applicable

Re: Select year in date range

Works perfect, thanks a lot!

Not applicable

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

Employee
Employee

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

Community Browser