topic Re: Invalid Expression in QlikView

Invalid Expression

manoranjan_d — Fri, 06 May 2016 06:45:36 GMT

Hi I mgetting Invalid expressiion for the below coding

TESTING:

Load Distinct

Max(Date(CREATED DATE,'MMDDYYYY')) as CREATED DATE,

AMT as AVAILABLE AMOUNT

Resident FROM TABLE1;

;

Re: Invalid Expression

tamilarasu — Fri, 06 May 2016 06:47:08 GMT

Hi Manoranjan,

All non-aggregated field names must be in a group by clause. Try like below,

Load Distinct

Max(Date(CREATED DATE,'MMDDYYYY')) as CREATED DATE,

AMT as AVAILABLE AMOUNT

Resident FROM TABLE1 Group by a, b, AMT ;

Re: Invalid Expression

phaneendra_kunc — Fri, 06 May 2016 06:51:15 GMT

‌You need a group by as Nagraj mentioned.

Re: Invalid Expression

Anonymous — Fri, 06 May 2016 06:56:32 GMT

TESTING:

Load Distinct

Max(Date([CREATED DATE],'MMDDYYYY')) as [CREATED DATE],

AMT as [AVAILABLE AMOUNT]

Resident FROM TABLE1

GROUP BY a, b, AMT;

Do not forget that you can't have any spaces on field names.

Re: Invalid Expression

florentina_doga — Fri, 06 May 2016 07:07:37 GMT

TESTING:

Load Distinct

Max(Date(CREATED DATE,'MMDDYYYY')) as CREATED DATE,

AMT as AVAILABLE AMOUNT

Resident TABLE1 group by

b, AMT;

TESTING:

Load Distinct

Max(Date(CREATED DATE,'MMDDYYYY')) as CREATED DATE,

sum(AMT) as AVAILABLE AMOUNT

Resident TABLE1 group by

use aggr function for amt

Re: Invalid Expression

Kushal_Chawda — Fri, 06 May 2016 07:28:27 GMT

when you are using any aggregation function in LOAD you should use Group by statement to aggregate the data on remaining fields.

In below LOAD statement you also need to use Sum on numerical fields like Amounts, Values. So your final LOAD should look like below

TESTING:

Load Distinct

Max(Date(CREATED DATE,'MMDDYYYY')) as [CREATED DATE],

Sum(AMT) as [AVAILABLE AMOUNT]

Resident FROM TABLE1

Group by a,b;

Re: Invalid Expression

sunny_talwar — Fri, 06 May 2016 09:06:18 GMT

or may be you want to pick the amount associated with the maximum date only

TESTING:

LOAD a,

Max(Date([CREATED DATE],'MMDDYYYY')) as CREATED DATE,

FirstSortedValue(AMT, -[CREATED DATE]) as AVAILABLE AMOUNT

Resident FROM TABLE1

Group By a, b;

One thing to note: since you are doing Group By, you don't really need Distinct. Because the resulting table will only have distinct rows for all combinations of your fields within Group By clause.