Max Value and Max Date and Group By in Load Script

[reporting_neu]

Hallo together,

I have three values:

OrderID
DeliveryID
DeliveryDate

The table looks like:

OrderID	DeliveryID	DeliveryDate
200450	100	10.12.2020
200450	200	11.12.2020
200450	250	12.12.2020
200500	300	12.12.2020
200550	400	13.12.2020

I want to GROUP the OrderID and have the MAX value of DeliveryID and DeliveryDate.

Result:

OrderID	DeliveryID	DeliveryDate
200450	250	12.12.2020
200500	300	12.12.2020
200550	400	13.12.2020

It works fine in SQL:

SELECT
     OrderID
   , MAX(DeliveryID)   AS DeliveryID
   , MAX(DeliveryDate) AS DeliveryDate
FROM relfbl
GROUP BY OrderID

In Qlik it takes a long time to load and the result is only one value of DeliveryID.

In Qlik it looks like:

Orders:
LOAD
      OrderID
    , MAX(DeliveryID)                           AS DeliveryID
    , MAX(DATE(FLOOR(TIMESTAMP(DeliveryDate)))) AS DeliveryDate
FROM [$(vExtractPath)/relfbl.qvd](qvd)
GROUP BY OrderID;

Can you help me?

[tm_burgers]

I always do it as a separate table, and then peek to load the field as a Variable... not sure if it helps!

[tmp_max_DeliveryID]:
Load
max(DeliveryID) as MaxDeliveryID
resident Orders;

let vMaxDeliveryID = Peek('MaxDeliveryID',0,'tmp_max_MaxDeliveryID');

drop table [tmp_max_MaxDeliveryID];

[reporting_neu]

Thank you for your support.

But with your script I only get the max value from the entire table. So only one value in total.

[tm_burgers · Accepted solution]

Oh, my apologies, I misinterpreted what you were looking for. 

In that case I'm not too sure why your script as written isn't working. 

I used one of my data sets that is similar with 3M rows, was 11seconds to load normally, and then added in the group by and aggregations similar to your script - it slowed it down but only to 20seconds.

Doing it in two steps like below brought the load time back down to 11second though:

tmp_Orders:
LOAD
      OrderID
    , DeliveryID
    , DeliveryDate
FROM [$(vExtractPath)/relfbl.qvd]
(qvd);

Orders:
LOAD
      OrderID
    , MAX(DeliveryID)                           AS DeliveryID
    , MAX(DATE(FLOOR(TIMESTAMP(DeliveryDate)))) AS DeliveryDate
Resident tmp_Orders
GROUP BY OrderID;

drop table tmp_Orders;

[reporting_neu]

Shame on me!!! 😆

After trying a thousand times to get the max values, I see that there are letters in the number. Of course it doesn't work!. I'll cut the letters off before and then it will work.
Please excuse me for missing this error. 🙄