Backorders - remove duplicates entries and keep the most recent one

[charlie2]

Hi everyone,

I have a Table in a .csv which contains information on back orders. More specifically, it contains the Order number, the Position of the order, the quantities which are in back order, and the date the file was updated. This file is every day update and new records are entered below the other ones. As an example, the table is structured as followed:

backorders:

load 

order_num & '_' & position AS primary_key,

order_num,

position,

qty_open,

update_date

FROM ....

My goal is to know the back orders today but also keep the historical development of these. My idea was to first filter the backorders from today with a WHERE clause and then load the past backorders and load a binary code in each table to indicate if these correspond to the past or to today.  However, a backorder with a past date that has not been resolved yet will also be entered as a record today. For example, two records might look like that:

order_num	position	qty_open	update_date
123456	10	50	13.06.2022
123456	10	50	14.06.2022

This indicates that the backorder was not yet resolved and today (14.06.2022) it is still open. How can I make sure that in these cases I only load the most recent one and not duplicate them?

Any help is more than appreciated

Thanks!

[justISO]

Hi, you can load order info just for max update date:

NoConcatenate
main:
LOAD 
primary_key,
order_num,
position,
qty_open,
max(update_date) as max_update_date,
if (today()=max(update_date), 'today', 'older') as indicator
RESIDENT backorders
GROUP BY primary_key, order_num, position, qty_open;

drop table backorders; 

additionally you can add indicator to check/filter today and older orders.

[charlie2]

Hi,

Thanks for your quick reply!

I already thought about adding this indicator. However, in the example above, the old record would be counted as an old record. However, this is not an old record, as the back order is still open (today there is still a back order). I just need in these cases not to upload the old records.

Thanks!