topic Max and group by in QlikView

Max and group by

Fri, 05 Feb 2016 09:19:06 GMT

Dear Community,

I'm trying to join a summary table that picks up the latest transaction to my main table but I get the error "invalid expression" and I'm not sure why?

LastException:
Left Join (Quote)
Load
Analysis.acode as LastException,
Analysis.quote as quote,
max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where left(Analysis.acode,3)='EXC'
Group by Analysis.quote;

Please can you help?

Best regards

Drew

Re: Max and group by

sunny_talwar — Fri, 05 Feb 2016 09:21:13 GMT

You need all non-aggregating fields in your group by statement. (Add Analysis.acode to your group by statement)

LastException:
Left Join (Quote)
LOAD Analysis.acode as LastException,
Analysis.quote as quote,
Max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where Left(Analysis.acode,3)='EXC'
Group By Analysis.quote, Analysis.acode;

Re: Max and group by

tamilarasu — Fri, 05 Feb 2016 09:21:43 GMT

Try,

Re: Max and group by

sunny_talwar — Fri, 05 Feb 2016 09:23:11 GMT

or this if you don't want to aggregate by Analysis.acode, but just use it for excling stuff:

LastException:
Left Join (Quote)
LOAD Analysis.quote as quote,
Max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where Left(Analysis.acode,3)='EXC'
Group By Analysis.quote;

Re: Max and group by

Fri, 05 Feb 2016 09:24:51 GMT

hi,

Re: Max and group by

jagan — Fri, 05 Feb 2016 09:33:17 GMT

Hi,

You have to specify all the fields in Group by which you are using Select