Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂 ❤️
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 .....;
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
;