Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
sunny_talwar

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
;

tamilarasu
Champion
Champion

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;

sunny_talwar

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
Author

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;

jagan
Luminary Alumni
Luminary Alumni

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;