14 Replies Latest reply: Apr 13, 2012 2:52 AM by qlik_techie RSS

Select year in date range

djibiont

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
    Henric Cronström

    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
      djibiont

      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
        Henric Cronström

        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
          djibiont

          Thanks for the explanation!

          • Re: Select year in date range
            djibiont

            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
              Henric Cronström

              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
                djibiont

                Works perfect, thanks a lot!

              • Select year in date range
                qlik_techie

                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
                  Henric Cronström

                  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
                    qlik_techie

                    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
                      Henric Cronström

                      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
                        qlik_techie

                        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
                          Henric Cronström

                          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