Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi!
Add Balance to Group By section
something like this
LOAD
[RMID] ,
sum(Balance) as Balance,
max(Date) as MaxPeriodDate
Resident Input
group by[RMID]
Drop table Input ;
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] ;
hi kush,
well i dont want to do the sum of balance i want to show it as it is in the max(PeriodDate)
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
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
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.
Hi
Use
FirstSortedValue(Balance,-Date) as Balance,
HTH
Sasi
Why we use FirstSortedValue? Please explain. Thanks in advance.