Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Provide guidance using Order, Inventory, and Incoming Supply data to clean up SAP

Skylarhaws
6 - Meteoroid

Hello Alteryx world,

 

First ever post here. I am stuck trying to figure out how to proceed...I have a list of open overdue orders from our SAP system, I also have an Inventory number from the same system. I finally have an incoming supply plan for the next 8 weeks.

 

What I am trying to figure out is how to calculate when orders can be filled based on inventory and incoming supply. 

 

I worked on Challenge 65 here to help me understand how to calculate a remaining inventory here:

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-65-Fulfilling-Inventory-Orders/m-p/54168

 

Now my challenge is taking the left over orders that were not able to be filled with current inventory and use the incoming supply plan to figure out when it will be supplied. I have provided some mock data of what I am working with and what I am trying to get to.

 

How can I do this? Any ideas?

 

Where I am at right now....

Order NumberScheduled Delivery Date

Material

Qty

Remaining

Filled?

When can be Filled?

12019-03-1011090YesImmediate
22019-03-15115090No 
32019-04-0311080YesImmediate
42019-04-1011070YesImmediate
52019-04-201700YesImmediate
62019-04-3012000

No

 

72019-05-051500No 
82019-05-0711000No 
92019-05-091200No 

 

Incoming Supply Data

Material2019-07-222019-07-292019-08-052019-08-122019-08-192019-08-262019-09-022019-09-09
1500015000300200

 

Outcome

Order NumberScheduled Delivery Date

Material

Qty

When can be Filled?

12019-03-10110Immediate
22019-03-151150 2019-08-12
32019-04-03110Immediate
42019-04-10110Immediate
52019-04-20170Immediate
62019-04-301200

 2019-09-02

72019-05-05150 2019-07-22
82019-05-071100 2019-09-02
92019-05-09120 2019-09-09 

 

4 REPLIES 4
andyuttley
11 - Bolide
11 - Bolide

Hi @Skylarhaws 

 

Interesting one. I’ve attached an iterative macro solution here (and added an 'Order 10' for testing):

IterateInv.PNG

 

 

Note that the way I’ve built this is to is to fulfill orders in delivery date ascending, so prioritizing the earliest unfulfilled order and waiting until it can fulfill that. Therefore the iterative macro will take one row each time (the earliest unfulfilled order) and process from there. 

 

It looks like in your example that you’re fulfilling a later unfulfilled order (with lower [Qty]) where possible, even though this impacts results further downstream. E.g. you're fulfilling order 7 before the others, which later means order 9 isn't fulfilled until a later date than mine (mine 2 Sep, yours 9 Sep). My concern with this approach would be that a massive unfulfilled order might never get served because it would forever prioritize orders way in the future, for example... but the reverse could be said of the first (attached) approach too

 

Obviously both are viable, if it's the latter that you're after then hopefully you can still just tweak this iterative macro into prioritizing how you want (e.g. sorting by Qty, or changing the filters in the macro)

 

Either way, hope this is still some help!

Andy 

Skylarhaws
6 - Meteoroid

Hey @andyuttley

 

This is great!

 

Yes, that is a concern that I was also thinking about, but left that out of the conversation for now because I was trying to figure out how to approach it. In this particular business use case I was thinking of applying additional logic that would take a split based on % of Global forecast for that specific country and apply that to the remaining Qty and then continue allocating down the line. This be a good balance of maximizing the number of orders while partially filling the large orders. If I did that I would need to figure out how to split the line with the decremented Qty so the remaining can be filled later.

 

The problem gets pretty complex the deeper I go.

 

Thanks for the reply. This does answer the original question. 

Skylarhaws
6 - Meteoroid

@andyuttley

 

I just realized something. Your solution provides an answer shows 370 can be filled on 2 Sep. when only 300 will come in. I am trying to figure out how the flow is calculating this. Does this keep a running total of everything being received and then filling the order? When I am looking at what has been filled vs. what has come in I am only able to come up with a max of 350. Do you happen to have any insight on this?

andyuttley
11 - Bolide
11 - Bolide

Hi @Skylarhaws 

 

I think this is correct though (if my understanding of the problem is right - please feel free to correct me if not!)

 

The reason being:

- we have to fulfill 90 remaining (from order 2); to do this we use 50 (from 22 July) + 40 of the 150 (from 12 Aug). This gives us 110 left over (from 12 Aug) to carry forward to our next rows.

- so on 2 Sep we have 300 come in, but + the 110 carried over = 410 to play with. We need 370 to fulfill orders 6, 7, 8 and 9 (=370), which is < 410

 

This is why I’m using the running total in the iterative macro, to reflect that we may have more than the exact value that came in that day – am I missing something though? Feel free to correct me if I am!

 

Andy

 

Labels