Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Max and group by

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

1 Solution

Accepted Solutions

Re: Max and group by

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
;

View solution in original post

5 Replies

Re: Max and group by

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
;

View solution in original post

Re: Max and group by

Try,

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.acode, Analysis.quote;

Re: Max and group by

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;

Not applicable

Re: Max and group by

hi,

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;

MVP & Luminary
MVP & Luminary

Re: Max and group by

Hi,

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

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;