Min and Max Date in load script

[#1]

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,

[#2]

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);

[#3]

For some reason Qlikview deleted the post from you

[#4]

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

[MayilVahanan]

Hi

     Try like this,

     Min(Date(

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

     I hope it helps you

[#6]

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>?

[#7]

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

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

[#8 · Accepted solution]

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;