topic Re: Error in QlikView

Error

mario-sarkis — Fri, 31 Jul 2015 07:21:07 GMT

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

Re: Error

pokassov — Fri, 31 Jul 2015 07:22:14 GMT

Hi!

Add Balance to Group By section

Re: Error

Kushal_Chawda — Fri, 31 Jul 2015 07:24:19 GMT

something like this

LOAD

[RMID] ,

sum(Balance) as Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID]

Drop table Input ;

Re: Error

sasiparupudi1 — Fri, 31 Jul 2015 07:25:48 GMT

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] ;

Re: Error

mario-sarkis — Fri, 31 Jul 2015 07:26:33 GMT

hi kush,

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

Re: Error

Kushal_Chawda — Fri, 31 Jul 2015 07:32:11 GMT

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

Re: Error

sasiparupudi1 — Fri, 31 Jul 2015 08:18:30 GMT

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

Re: Error

qlikviewwizard — Fri, 31 Jul 2015 08:32:31 GMT

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.

Re: Error

sasiparupudi1 — Fri, 31 Jul 2015 08:38:39 GMT

Use

FirstSortedValue(Balance,-Date) as Balance,

HTH

Sasi

Re: Error

qlikviewwizard — Fri, 31 Jul 2015 09:06:16 GMT

Hi SasiParupudi1

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

Re: Error

Fri, 31 Jul 2015 09:11:38 GMT

Hi ,

try this

LOAD

[RMID] ,

Balance,

max(Date) as MaxPeriodDate

Resident Input

group by[RMID],Balance;

Drop table Input ;

in group by we have to Qualify the all remaining fields which are are there in the Table

Re: Error

antoniotiman — Fri, 31 Jul 2015 09:39:48 GMT

Hi Mario,

Try

Inner Join Load

[RMID],

Max(Date) as MaxPeriodDate

Resident Temp

Group By [RMID];

Besides, are You sure Date field is date Type ?