Join Tool - 2 Criteria's in File B

EmilAlteryx01

Help to create a workflow that compares File A and File B — and outputs only those records where File A has the same brand ID and quantity that is either the same or less than the quantity in File B (Only Out)

Match records from File A to File B where:

File A’s Brand ID matches File B’s Brand, and
File A’s Quantity is ≤ File B’s Quantity.

File A
Shoe ID	Show Quantity	Type
4274M101	2497	SHOE
1111S251	85000	SHOE
1111S251	9587	SHOE
4274M101	4677	SHOE
4274M101	847	SHOE
4274M101	13550	SHOE
1111S251	25000	SHOE
4274M101	12199	SHOE
4274M101	2711	SHOE
4274M101	1586	SHOE
4274M101	21295	SHOE
4274M101	61099	SHOE
4274M101	5836	SHOE

File B
Shoe ID	In/Out	Quantity
4274M101	I	0
4274M101	O	0
4274M101	I	-83000
4274M101	O	83000
1111S251	I	-2000
1111S251	O	2000
4274M101	I	0
4274M101	O	0
4274M101	I	-114700
4274M101	O	114700
4274M101	I	-110500
4274M101	O	110500
4274M101	I	-143000
4274M101	O	143000
4274M101	I	-263300
4274M101	O	263300
1111S251	I	-8000
1111S251	O	8000

Expected Output - : 1 Worksheet Label Shoe Check (2 Tabs labelled - WareHouse OK and - To Be ReChecked)
4274M101 (126297 ≤ 714500) ✅ →  WareHouse OK
1111S251 (126297 > 10000) ❌ → To Be ReChecked

alexnajm

I think you highlighted to logic pretty well, so I would just start with each part and work through it

• "Only Out" --> filter the data down on [In/Out]="O"
• Aggregate the data on each side to the summed amounts per Shoe ID - Summarize tools
• Join on Shoe ID
• Formula to calculate column based on summed show quantity versus summed quantity to split into "Warehouse Ok" versus "To be Rechecked"

Try it out and let us know what questions you have!