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: 
mario-sarkis
Creator II
Creator II

Error

LOAD

[RMID] ,

Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID],PeriodMonth;

Drop table Input ;

Hey i am writing this in the Script it told me an Error:

"Invalid Expression"

what is wrong with it ?

Help please

1 Solution

Accepted Solutions
qlikviewwizard
Master II
Master II

Hi

Use the below script.

LOAD

[RMID] ,PeriodMonth

sum(Balance) as Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID],PeriodMonth;

Drop table Input ;


This will works. If not,please provide full script.

View solution in original post

11 Replies
pokassov
Specialist
Specialist

Hi!

Add Balance to Group By section

Kushal_Chawda

something like this

LOAD

[RMID] ,

sum(Balance) as Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID]

Drop table Input ;

sasiparupudi1
Master III
Master III

There is no periodmonth in your select list..

may be

LOAD

[RMID] ,

sum(Balance) as Balance

max(Date) as MaxPeriodDate

Resident Input

group by[RMID] ;

mario-sarkis
Creator II
Creator II
Author

hi kush,

well i dont want to do the sum of balance i want to show it as it is in the max(PeriodDate)

Kushal_Chawda

then remove PeriodMonth from group by. as below will give you RMID wise max date and balance.

LOAD

[RMID] ,

Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID]

Drop table Input

if you want PeriodMonth  wise then  add PeriodMonth  in load statement


LOAD

[RMID] ,

Balance,

PeriodMonth  ,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID],PeriodMonth 

Drop table Input

sasiparupudi1
Master III
Master III

Bottom line is you cannot use naked fields in an aggregation like group by.. If you wrap amount in sum or max,you will not get the error..

HTH

qlikviewwizard
Master II
Master II

Hi

Use the below script.

LOAD

[RMID] ,PeriodMonth

sum(Balance) as Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID],PeriodMonth;

Drop table Input ;


This will works. If not,please provide full script.

sasiparupudi1
Master III
Master III

Hi

Use

FirstSortedValue(Balance,-Date) as Balance,

HTH

Sasi

qlikviewwizard
Master II
Master II

Hi SasiParupudi1

Why we use FirstSortedValue? Please explain. Thanks in advance.