Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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);
For some reason Qlikview deleted the post from you
Have you tried specifying the date format? i.e. Date(YourField,'mm/dd/yyyy')
Hi
Try like this,
Min(Date(
[EmployeeDirectory.DOH],'DD/MM/YYYY'))
I hope it helps you
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>?
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
.......................
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;