Updating Data Table Status Based on Matching Values from Status Table

[pkirr]

I have a data table and a status table. My goal is to compare these two tables and update the status in the data table whenever there's a match with the status table. Importantly, not all columns need to match between the two tables. I want to avoid using the append function as it can lead to unnecessary data inflation. I'm considering using a macro to achieve this, but I'm unsure of how to set it up. Can someone provide guidance on how to create a macro for this purpose?

Status table

start date	end date	origin id	origin country	service level	carrier scac	status
2023-01-01	2023-12-31	12345				exception
2023-09-01	2023-12-31	55446		TL		no charge
2023-04-01	2023-07-31		CAN		ABC	exception

Data table

create date	origin id	origin country	service level	carrier scac	status
2023-07-24	55446	CAN	TL	ABC	normal
2023-07-25	55446	CAN	LTL	DEF	normal
2023-07-25	12345	USA	TL	ABC	normal

Thank you,

Paul

[gawa]

Hi @pkirr 

With the given context, not clear about your goal but I can advise a couple of general things...

When you compare two tables, first consider to use JOIN tool instead of Appendix Field. By properly selecting key field/s, it will not generate unnecessary records(and also not generating unnecessary columns by uncheck unnecessary columns in configuration of JOIN tool). For matched data from J-anchor you can change some data(in your case "Status"). After that, you just UNION data from L-anchor and J-anchor so as to retrieve the original format.

Record ID tool is useful to sort data in original order after a couple of JOIN/UNION operation, since original order will not maintain after those operations.

Refer to attached workflow for instance. In this case, first matched condition is equality of "origin_id" of two tables.

After then, if "create date" is between "start_date" and "end date", change status to one form another table.

[Qiu]

@pkirr 
What would be your matching creteria?

Aside from date, should we also consider one of the following or all of them?

origin id

origin country

service level

carrier scac