invalid expression- why?

[ioannagr]

LOAD

max(num(id)),
orderdate,

applymap('MapOrderStatus', orderstatus, '-')  as orderstatus,
applymap('MapcheckProcess',checkprocess, '-') as checkprocess
statusdate,

lastupdate,

num(unitnumber) as unitnumber;

SQL SELECT "id",
"orderdate",
"orderstatus",
"checkprocess",
"statusdate",
concat(unit_place,unit_year,RIGHT(CONCAT('000000', unit_code),6)) as unitnumber,
"lastupdate"
FROM "public"."record_orders"
group by unitnumber,orderdate,id, orderstatus, checkprocess,statusdate,lastupdate;

Hello, the above is my script.

I just want to get one max(id) per unitnumber but neither groupping by unitnumber alone isn't possible as it seems, nor groupping by all of the above.

I could use your help!

Thank you in advance 🙂 ❤️

[jwjackso]

You group statement is for the SQL, not the Qlik load.  I think you need to add a group statement in the Qlik load.

LOAD

max(num(id)),
orderdate,

applymap('MapOrderStatus', orderstatus, '-')  as orderstatus,
applymap('MapcheckProcess',checkprocess, '-') as checkprocess
statusdate,

lastupdate,

num(unitnumber) as unitnumber

group by .....;

[vamsee]

May be try

Source:
LOAD

Num(id) as id,
orderdate,

applymap('MapOrderStatus', orderstatus, '-')  as orderstatus,
applymap('MapcheckProcess',checkprocess, '-') as checkprocess
statusdate,

lastupdate,

num(unitnumber) as unitnumber;



SQL SELECT "id",
"orderdate",
"orderstatus",
"checkprocess",
"statusdate",
concat(unit_place,unit_year,RIGHT(CONCAT('000000', unit_code),6)) as unitnumber,
"lastupdate"
FROM "public"."record_orders"
group by unitnumber,orderdate,id, orderstatus, checkprocess,statusdate,lastupdate;
Left Join(Source)
 Load
Max(id) as Max_ID
Resident Source
;