topic Re: Backorders - remove duplicates entries and keep the most recent one in App Development

Backorders - remove duplicates entries and keep the most recent one

charlie2 — Tue, 14 Jun 2022 06:19:48 GMT

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!

Re: Backorders - remove duplicates entries and keep the most recent one

justISO — Tue, 14 Jun 2022 07:33:20 GMT

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.

Re: Backorders - remove duplicates entries and keep the most recent one

charlie2 — Mon, 20 Jun 2022 11:35:23 GMT

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!