Invalid Expression

[manoranjan_d]

Hi I mgetting Invalid expressiion for the below coding

TESTING:

Load  Distinct

a,

b,

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

AMT as AVAILABLE AMOUNT

Resident FROM TABLE1;

;

[tamilarasu]

Hi Manoranjan,

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

Load  Distinct

a,

b,

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

AMT as AVAILABLE AMOUNT

Resident FROM TABLE1 Group by a, b, AMT ;

[phaneendra_kunc]

‌You need a group by as Nagraj mentioned.

[Anonymous]

TESTING:

Load  Distinct

a,

b,

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.

[florentina_doga]

TESTING:

Load  Distinct

a,

b,

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

AMT as AVAILABLE AMOUNT

Resident TABLE1 group by

a,

b, AMT;

or

TESTING:

Load  Distinct

a,

b,

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

sum(AMT) as AVAILABLE AMOUNT

Resident TABLE1 group by

a,

b;

use aggr function for amt

[Kushal_Chawda]

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

a,

b,

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

Sum(AMT) as [AVAILABLE AMOUNT]

Resident FROM TABLE1

Group by a,b;

[sunny_talwar]

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

TESTING:

LOAD a,

          b,

          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.