Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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
2 Replies
Gysbert_Wassenaar
Not applicable

Re: Select a Year Range based on Two Dates.

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;


talk is cheap, supply exceeds demand
Not applicable

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.