Aggregation expressions required by GROUP BY clause

[#1]

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

[zhadrakas]

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;

[#3]

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

[zhadrakas]

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/b...

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

[Peter_Cammaert]

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