Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

14 Replies
Not applicable
Author

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

hic
Former Employee
Former Employee

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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

Not applicable
Author

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