Condition check Loop -- Determining the threshold where loop fails

Question

Hi everyone, I've just started using Altrex after messing my head trying to solve the problem using Excel/Power Bi. Though this tool is super handy and fun, I am struggling with mid-advance level queries.

My data set is about Shipments with variables including Source location, Destination location, Item, Shipment Dates, Shipment Types and then I have Shipment Quantity, Inventory On-hand and Safety Stock level at each Location for each item.

What I want is to sum-up those individual shipments for each network node (Source-Destination) and for each Item at weekly level and then compare it against Source location current Inventory On-hand to see how much can I afford to ship after maintaining certain level of safety stock (which also varies according to shipment type).

So basically, I am trying to build a workflow that would flag the upper threshold of bridged/consolidated shipments where the available inventory does not allow (Available stock <= Bridged shipment volume). In concept, it looks like a loop but not sure if there is another way to approach the solution.

PS: Case and Pallet Sizes are there as additional values that would be used at a later stage (so Ignore).

Bazal · Answer

I hope this helps.

Weekly Merger Testing.yxzp

Interm Flow.yxzp

rzdodson · Answer

@Bazal when able, please upload the two previous workflows as yxzp files - this will allow others to see the initial data to help. To do that, go to Options > Export Workflow, then upload the files to this thread. As of now, I cannot run either of the files that you sent over in your last post.

Bazal · Answer

The end goal is to bridge as many shipments as possible while still respecting the safety stock (SS) parameters. These shipments are raised by system automatically as per the set logic and we are now trying to reduce the frequency of such with higher volume such that warehouse order processing cost and logistics cost is lower.

I have attached the workflows where one is combining data from two sources: Inventory picture and Shipments, and the other is where the actual flow is being built.

Mind you, the SS thresholds are different for different run types so for example if the Loop flows through first round, it might now have to compare against a different input threshold.

One way I think of the solution is to calculate for remaining On-hand (OH) at Source location for each Item and continue the loop for all the Run Types and Weeks, respecting each round's SS threshold, until either all rounds are done or all Items are dropped (threshold not met).

Rounds:
Week 1 RunType1
W1+2 RT1
W1+2+3 RT1
(W1+2+3 RT1) + W1 RT2

So on so forth; total 3 Weeks and 4 Run Types.

Weekly Merger Testing.yxwz

Interm Flow.yxwz