topic Re: Min and Max Date in load script in QlikView

Min and Max Date in load script

Tue, 14 Aug 2012 21:37:33 GMT

Anyone tell me why this expression is failing on load. The only descriptive error I get is invalid expression. Thank you in Advance.

min(date([EmployeeDirectory.DOH])) as MinDate,

max(date([EmployeeDirectory.DOH])) as MaxDate,

Re: Min and Max Date in load script

Tue, 14 Aug 2012 21:46:29 GMT

In this script which fields go in the group by? All of them?

Employees:

LOAD EmployeeDirectory.ID,

EmployeeDirectory.Name

EmployeeDirectory.Name as Employee,

EmployeeDirectory.DOH,

date(min([EmployeeDirectory.DOH])) as MinDate,

date(max([EmployeeDirectory.DOH])) as MaxDate,

year(EmployeeDirectory.DOH) as DOH_Year,

[EmployeeDirectory.Start Date in Role - Actual],

today()-[EmployeeDirectory.Start Date in Role - Actual] as EmployeeDirectory.ActualTenure,

today()-[EmployeeDirectory.DOH] as EmployeeDirectory.DOHTenure,

[EmployeeDirectory.Inherited Business Offset],

if(isnull([EmployeeDirectory.Inherited Business Offset]),0,[EmployeeDirectory.Inherited Business Offset]) as [EmployeeDirectory.Inherited Business Offset Number],

[EmployeeDirectory.NLE Date],

Year([EmployeeDirectory.NLE Date]) as NLE_Year,

if([EmployeeDirectory.NLE Date]>0,1,0) as NLEIndicator,

if([EmployeeDirectory.NLE Date]>0,'Yes','No') as NLEFlag,

[EmployeeDirectory.Employee Target Offset],

[EmployeeDirectory.Boot Camp Graduation Date],

[EmployeeDirectory.Sales/BP],

[EmployeeDirectory.Current Days Added Goal],

EmployeeDirectory.Team as Team,

[EmployeeDirectory.Job Title],

[EmployeeDirectory.Job Category],

[EmployeeDirectory.Senior BP],

[EmployeeDirectory.Senior Sales],

EmployeeDirectory.FTE,

EmployeeDirectory.Username,

[EmployeeDirectory.Cubicle Number],

[EmployeeDirectory.Last Updated],

EmployeeDirectory.Side,

EmployeeDirectory.Remote

[\\slcnasp01\Qlikview_Data\qliktech_user\DavidHill_Dev\2_Loaders-QVW\QVDs\EmployeeDirectory.qvd]

(

qvd);

Re: Min and Max Date in load script

Tue, 14 Aug 2012 21:47:01 GMT

For some reason Qlikview deleted the post from you

Re: Min and Max Date in load script

Tue, 14 Aug 2012 21:49:48 GMT

Have you tried specifying the date format? i.e. Date(YourField,'mm/dd/yyyy')

Re: Min and Max Date in load script

MayilVahanan — Tue, 14 Aug 2012 21:58:45 GMT

Try like this,

Min(Date(

[EmployeeDirectory.DOH],'DD/MM/YYYY'))

I hope it helps you

Re: Min and Max Date in load script

Tue, 14 Aug 2012 22:03:39 GMT

I know it has something to do with using a Min and Max date in a loadscript without specifing a group by. I'm not sure how many fields I would have to include in my group by to get it to work>?

Re: Min and Max Date in load script

Wed, 15 Aug 2012 13:20:48 GMT

Try just the following and see if your min/max pull:

LOAD EmployeeDirectory.ID,

EmployeeDirectory.Name as Employee,

EmployeeDirectory.DOH,

min(date([EmployeeDirectory.DOH],'mm/dd/yyyy')) as MinDate,

max(date([EmployeeDirectory.DOH],'mm/dd/yyyy')) as MaxDate,

FROM

.......................

Re: Min and Max Date in load script

Thu, 16 Aug 2012 15:13:39 GMT

The Min and Max function normally only work with a group by unless you use a Resident statement. Looks like this: Works like a champ. Thank you everyone.

TEMP:

LOAD

num(min(EmployeeDirectory.DOH)) AS MinDate,

num(max(EmployeeDirectory.DOH)) AS MaxDate

RESIDENT

Employees

;

LET vMinDate = peek('MinDate', 0, 'TEMP');;

LET vMaxDate = peek('MaxDate', 0, 'TEMP');;

SET vRangeStart = =vMinDate;

SET vRangeEnd = =vMaxDate;