Select a Year Range based on Two Dates.

Mon, 12 Nov 2012 10:39:52 GMT

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.

Product	MemNum	BenfcNum	EffDate	EffToDate	Check
Group1	932001	0	2012/01/01	9999/12/31	Furure Dated
Group1	932001	1	2012/01/01	9999/12/31	Furure Dated
Group1	932001	2	2012/01/01	9999/12/31	Furure Dated
Group1	562221	0	2012/06/01	NULL	NULL
Group1	562221	1	2012/06/01	NULL	NULL
Group1	562221	2	2012/06/01	NULL	NULL
Group1	854414	0	2011/01/01		''
Group1	854414	1	2011/01/01		''
Group1	854414	2	2011/01/01		''
Group1	821112	0	2009/01/01	2012/06/30	Not Active
Group1	821112	1	2009/01/01	2012/06/30	Not Active
Group1	821112	2	2009/01/01	2012/06/30	Not Active

Re: Select a Year Range based on Two Dates.

Gysbert_Wassenaar — Mon, 12 Nov 2012 11:50:47 GMT

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.

Mon, 12 Nov 2012 11:56:13 GMT

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.

topic Re: Select a Year Range based on Two Dates. in QlikView

Select a Year Range based on Two Dates.

Re: Select a Year Range based on Two Dates.

Re: Select a Year Range based on Two Dates.