topic Re: Aggregation expressions required by GROUP BY clause in QlikView

Aggregation expressions required by GROUP BY clause

Thu, 23 Mar 2017 10:07:43 GMT

Dear All,

Please check the below Code

Getting Aggregation Error when i join the Code which is in Bold.

EmpAttendanceLogs:

//Concatenate(EmpAttendance)

LOAD

Uniqueid as EmpAttedenceLogID,

EmployeeUniqueId as EmpLogUniqueid,

EmployeeUniqueId &Date(Floor(Num(AttendenceDate))) as CompositeKey,

EmployeeUniqueId &Date(Floor(Num(AttendenceDate))) as CompositeKey1,

ApplyMap('Mapping_Employee',EmployeeUniqueId) as EmployeeAttendenceLogName,

Date(Floor(Num(AttendenceDate))) as EmpAttendenceLogDate,

AttendenceTime as EmpAttendenceLogTime,

Type as LogType,

Status as RegularizedStatus,

IsRegularized

FROM

[..\10_data_sources\EmpAttendanceLogs.qvd]

(qvd);

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

Date(Min(EmpAttendenceLogTime),'hh:mm:ss') as RegInTime,

Date(Max(EmpAttendenceLogTime),'hh:mm:ss') as RegOutTime

Resident EmpAttendanceLogs where IsRegularized=1

Group by EmpLogUniqueid,EmpAttendenceLogDate,RegularizedStatus;

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

Date(Min(EmpAttendenceLogTime),'hh:mm:ss') as LogInTime,

Date(Max(EmpAttendenceLogTime),'hh:mm:ss') as LogOutTime,

Interval(Date(Max(EmpAttendenceLogTime),'hh:mm:ss')-Date(Min(EmpAttendenceLogTime),'hh:mm:ss') ) as LogDuration

Resident EmpAttendanceLogs where IsRegularized=0

Group by EmpLogUniqueid,EmpAttendenceLogDate,RegularizedStatus;

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

If(Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss'))>0,Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),0) as LogInDuration,

If(LogOutTime=Time('00:00:00','hh:mm:ss'),Interval(Time(RegOutTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),

If(Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss'))>0,

Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss')),0)) as LogOutDuration

Resident EmpAttendanceLogs

Group by EmpLogUniqueid,EmpAttendenceLogDate,RegularizedStatus;

Getting below error when i join the above code.

Error:

Aggregation expressions required by GROUP BY clause

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

If(Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss'))>0,Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),0) as LogInDuration,

If(LogOutTime=Time('00:00:00','hh:mm:ss'),Interval(Time(RegOutTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),

If(Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss'))>0,

Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss')),0)) as LogOutDuration

Resident EmpAttendanceLogs

Group by EmpLogUniqueid,EmpAttendenceLogDate,RegularizedStatus

Regards,

Keerthi KS

Re: Aggregation expressions required by GROUP BY clause

zhadrakas — Thu, 23 Mar 2017 10:40:46 GMT

you are using Group by without any Aggregation functions.

try without Group by:

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

Date(Min(EmpAttendenceLogTime),'hh:mm:ss') as RegInTime,

Date(Max(EmpAttendenceLogTime),'hh:mm:ss') as RegOutTime

Resident EmpAttendanceLogs where IsRegularized=1;

Re: Aggregation expressions required by GROUP BY clause

Thu, 23 Mar 2017 11:40:40 GMT

I need to have group by function here.

Left Join (EmpAttendanceLogs)

Load

EmpLogUniqueid,

EmpAttendenceLogDate,

RegularizedStatus,

If(Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss'))>0,Interval(Time(LogInTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),0) as LogInDuration,

If(LogOutTime=Time('00:00:00','hh:mm:ss'),Interval(Time(RegOutTime,'hh:mm:ss')-Time(RegInTime,'hh:mm:ss')),

If(Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss'))>0,

Interval(Time(RegOutTime,'hh:mm:ss')-Time(LogOutTime,'hh:mm:ss')),0)) as LogOutDuration

Resident EmpAttendanceLogs

Group by EmpLogUniqueid,EmpAttendenceLogDate,RegularizedStatus;

Can you please tell me what is the mistake here ???

Re: Aggregation expressions required by GROUP BY clause

zhadrakas — Thu, 23 Mar 2017 11:53:01 GMT

if you want to use Group by you Need to use an Aggregation function on each field which is not listed after the Group by Statement.

See this link for further Information to Aggregation functions.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/AggregationFunctions/basic-aggregation-fun…

use min/max as you did it in the first left join .

Re: Aggregation expressions required by GROUP BY clause

Peter_Cammaert — Thu, 23 Mar 2017 12:02:13 GMT

Let's go back a step and imagine what is going on in the Script Engine's head when it reaches this LOAD statement:

The Time() function accepts only one value as first parameter. For every possible combination of EmpLogUniqueId+EmpAttendanceLogDate+RegularizedStatus, you may get multiple different values in fields LoginTime, LogOutTime, RegInTime and RegOutTime. What should the Time() function do with these multiple values?

If you are sure that only one value is present in all fields, use Only() to simulate the use of an aggregation function, as in:

...Interval(Time(Only(RegOutTime),'hh:mm:ss')-Time(Only(RegInTime),'hh:mm:ss'))...

The Min(), Max() or FirstSortedValue() functions can be used to pick a specific datetime value from a series.

Best,

Peter