Building workflow for replacing VBA script

KNIME Analytics Platform
1

Hi there,
I have historical sales/order data which consist of SKU number, wish delivery date of order, order quantity, delivery date and stock quantity in wish delivery date of order. So, I need to calculate backorder quantity of each SKU in each wish delivery date of orders. Currently I am using VBA macro to calculate backorders in wish delivery dates but I want to use Knime to do that. sample data is attached

I will be appreciated for any help or tip
thanks in advance

backorder calculation example.xlsx (20.4 KB)

1 Like
2

Hi @Mehmet!

I am happy in trying to help you!

Can you explain to me how you compute the backorders? Is it just the Order quantity - the current quantity in stock?

Best wishes,
Ana

3 Likes
3

Hi @ana_ved,
I just consider order quantity in order to compute backorder.

  • each row refers to an Order document.
  • Purpose is that whether stock quantity is enough for all orders (including backorders) in wish delivery date
  • backorder is the order that has earlier wish delivery date and later delivery date or not delivered yet.
1 Like
4

Hi @Mehmet!

Thank you for the explanation! It was very helpful. But I am not sure I understand how you should compute the backorder quantity. For instance, why backorder quantity on row number 40 is 5 if the order was delivered before wish date?

If you have the chance, could you maybe share part of your VBA script?

Cheers,
Ana

2 Likes
5

Hi @ana_ved,
my vba coding is attached.
Although, both row 40 and row 41 and row42 is requested on 05.04.2019 , row 40 and row 41 was delivered before 05.04.2019 and row 42 was still waiting on wish delivery date that is 05.04.2019 and quantity is 5 pcs. Since Delivery date of row 42 is 14.05.2019, backorder in 05.04.2019 for that SKU is 5.
I hope I was able to explain clearly

VBA macro computing backorder and stock control.txt (1.0 KB)

6

Hi @Mehmet

I am still not 100% sure I understand the use case. However, here is an example workflow.

What it does: first reads the data, computes the time difference between wish delivery date and delivery date (so, if this number is negative, order was delivered before the wish delivery date). Then it does some type casting using the duration to Number and finally it sets a rule engine saying that if order was delivered after wish delivery date, the backorder quantity equals the ordered quantity.

backorder.knar (28.6 KB)

Even if this is not exactly what you want, it might be a good start.

Best wishes,
Ana

5 Likes
7

Hi @Mehmet,

I looked through your VBA code and took a stab at reproducing it.
I’ll briefly summarize what I tried to do.

  1. group into SKU sets
  2. for each record look at records that share the same SKU
  3. create backorder qty by summing order qty where wish <= req and delivery >= req
    *where req is the wish date of the row in question
  4. if stock qty > 97% of backorder qty 1 else 0 for stock control column

image
image1444×290 27.7 KB

Here’s the workflow example, it runs on the example you uploaded at the start of the thread.
Backorder Calculation.knwf (38.7 KB)

Does this seem like the correct interpretation? My calculation matches the backorder listed in your excel file on all rows.

Best of luck with your KNIME adventures!
Corey.

8 Likes
8

Hi @Corey,
It is what I exactly need. Thank you very much.

Best wishes,
Mehmet

4 Likes
9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.