2 Replies Latest reply: Nov 12, 2012 6:56 AM by Syed Khaled Shahbaaz RSS

    Select a Year Range based on Two Dates.

      HI

       

      I am new to QlikView. 

       

      I require the use

       

      I want to the user to be able to select a group /multiple groups and year/multiple years.  My table contains the groups but does not contain a year value - this needs to be calculated based on the data in the table (EffDate and EffToDate). 

       

      I used a List Box and populated it with the values from the product column - multiple selections can be made.  I have not problem setting this up.

       

      Next, I want a List Box or Input Box with a list of years.  Multiple selection is required.  Having trouble with this.

       

      I have the following Table:  Member Table containg the Group the Member belongs to, the Member Number, the Beneficiary Number, EffDate (date at which Member joined the Group), EffToDate (date at which Member left the group).

       

      Not the following:  EffToDate Column:  The date field can contain a date, eg Future Dated 9999/01/01, it can contain 'n NULL value or the Field can contain nothing.

       

      Also note:  The could be more than one group.

       

      Say for instance the following selection is made:  Product = Group 1 and Year = 2012:

       

      In SQL I would do the following check:  I require all Members active in 2012 for Group1:

       

       

      declare @StartDate datetime = '2012-01-01'

      declare @EndDate datetime = '2012-12-31'

      declare @Product char(50) = 'Group1'

       

      select distinct

      o.Product,

      o.MemNum,

      o.BenfcNum,

      o.EffDate,

      o.EffToDate

       

      from Table o

       

      where

      o.Product = @Product  and

      o.EffToDate< @EndDate and

      (

                     o.EffToDate > @StartDate or

                     o.EffToDate is null or

                     ltrim(rtrim(o.EffToDate)) = '' or

                     o.EffToDate > getdate()

      )

       

      Once I have the info, I would do a Distinct Count of MemNum + BenfcNum and Group by Product.

       

      The End Result should be a Distinct Count per Product based on the Product and Year selection the User has made.

       

      ProductMemNumBenfcNumEffDateEffToDateCheck
      Group193200102012/01/019999/12/31Furure Dated
      Group193200112012/01/019999/12/31Furure Dated
      Group193200122012/01/019999/12/31Furure Dated
      Group156222102012/06/01NULLNULL
      Group156222112012/06/01NULLNULL
      Group156222122012/06/01NULLNULL
      Group185441402011/01/01 ''
      Group185441412011/01/01 ''
      Group185441422011/01/01 ''
      Group182111202009/01/012012/06/30Not Active
      Group182111212009/01/012012/06/30Not Active
      Group182111222009/01/012012/06/30Not Active
        • Re: Select a Year Range based on Two Dates.
          Gysbert Wassenaar

          Try adding a year fields in the load script:

           

          load * ,

          year(EffDate) as EffYear,

          year(EffToDate) as EffToYear;

          select distinct

              o.Product,

              o.MemNum,

              o.BenfcNum,

              o.EffDate,

              o.EffToDate

          from Table o;

          • Re: Select a Year Range based on Two Dates.

            Hi,

             

            If it was a single Date field, you could simply calculate the Year field by using Year(EffDate) statement in the script.

            Since it seems you are using two dates (a From date and a To Date), you could try calculating the Year field like this:

             

            XYZ:

             

            Load *,

            Year(EffDate) as Year

            From Sourcefile;



            Concatenate

            Load *,

            Year(EffToDate) as Year

            From Sourcefile;

            This would give you a Year field containing year values from both Date fields. Or you could even try calculating all the years between the from and to dates (say, the min(year) from EffDate and Max(Year) from (EffToDate) and then evaluate year.

             

            Hope that helps.

             

            -Khaled.