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 · Accepted solution]

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!

[EmilAlteryx01]

Thanks Alex - how do i incorporate the below check thou - 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 

4274M101 (126297 ≤ 714500) ✅ → WareHouse OK
1111S251 (126297 > 10000) ❌ → To Be ReChecked

[EmilAlteryx01]

[alexnajm]

Great job so far!

As mentioned, a Formula to calculate column based on summed show quantity versus summed quantity to split into "Warehouse Ok" versus "To be Rechecked". You can't apply this with a join tool, so you need to calculate it and either use that column to split to your two outputs or filter on it and output from there

[Qiu · Accepted solution]

@EmilAlteryx01 
I think this is what @alexnajm means. 😁