Java Snippet

KNIME Analytics Platform
1

Hey guys!

I need help on a topic, have an order table already handled, need to manage stock.

This table will arrive in the Java Snippet and need to treat it:

ORDER CLIENT QT (ORDER) POSSIBLE STOCK QT (STOCK)
1 A 200 A1234B 50
1 A 200 A1335C 150
2 B 150 A123A 100
2 B 150 A124B 50
2 B 150 A125B 50
3 C 1250 A0972 250
3 C 1250 A0973 500
3 C 1250 A0974 250
3 C 1250 A0975 1500
3 C 1250 A0976 2000
4 D 100 B123 90
4 D 100 B124 5

In this table, grouped the orders and replicated them to possible stocks, so that within the Java Snippet I could calculate the QUANTITY (ORDER) - QUANTITY (STOCK).

In ORDER 1, we need stock A1234B and A1335C.
In ORDER 2, we need stocks A123A and A124B and we will not need stock A125B
…

I would like this result in output Java Snippet:

ORDER CLIENT QT (ORDER) POSSIBLE STOCK QT (STOCK) RESULT
1 A 200 A1234B 50 50
1 A 200 A1335C 150 150
2 B 150 A123A 100 100
2 B 150 A124B 50 50
2 B 150 A125B 50 0
3 C 1250 A0972 250 250
3 C 1250 A0973 500 500
3 C 1250 A0974 250 500
3 C 1250 A0975 1500 0
3 C 1250 A0976 2000 0
4 D 100 B123 90 90
4 D 100 B124 5 5
4 D 100 - INSUFFICIENT STOCK

For order 4, there was not enough stock and you must add a line and include this message “INSUFFICIENT STOCK”.

Remembering that there may be many stocks, but only what is necessary should be consumed.

And another question, I need to import the libraries in code? How do this?

Thanks!

2

Hi @Victor_Marguti , is there a particular reason why you want to use Java snippet for this?

Are you looking for some kind of cumulative stock calculation or just working on a row by row basis in terms of available stock?

Whilst the calculating can be done with Java snippet, you won’t be able to use it to add the additional rows. Along with many nodes, Java snippet cannot add new rows or delete them so at least part of this processing will need to be using other nodes. So back to my question about why the specific need to use Java snippet… There may be other better solutions, or at least some hybrid.

1 Like
3

Hello!

Thanks for the answer, there is no specific reason to use the Java Snippet, but I have this code in Python and I wouldn’t like to use it because it needs the extension, I wanted to use something native to knime.

If there is any other solution other than Java that would also work, but it cannot be Python either.

4

I would recommend to try the integrated python extension:

1 Like
5

Going back to your original question, when you say “we will not need stock A125”, what is it in the data that informs us of this? How would the solution know?

Likewise what does “only what is necessary should be consumed” mean? It feels like there are some additional pieces of information that we would need to determine “necessary”.

And now that I’m actually looking at your example numbers, I don’t actually understand your example “result” table.

You said it was QUANTITY (ORDER) - QUANTITY (STOCK)
but that would result in the follows calcs (using Math Formula node), so why wouldn’t all the negative lines here also need to say “insufficient stock”

image
image705Ă—327 8.03 KB

The more I look at your sample results table, the less I understand it. Sorry if I’m being slow today, but could you explain the requirement in more detail please.

Finally, which version of KNIME are you using (i.e. 4.7 or 5.1) .

btw, welcome to the KNIME community! :slight_smile:

1 Like
7

No problem!

Maybe I’m not very clear with the information, sorry :frowning:

Think about it like this…

Order 2 = Customer A - 150 Qt

To meet the quantity needed to supply Order 2, I have stock A123A (100 qt) A124B (50 qt) and A125B (50 qt). If the order is for 150 Qt, stocks A123A and A124B already resolve the issue and it would not be necessary to use A125B, because the required order quantity (150 Qt) has already been supplied with both stocks (A123A and A124B)

To define what would be only what is necessary, the order and customer column must be the basis, the order is 150 and I have two first stocks that meet the requirements, I will not need to take any more stock, if I have several stocks but the first one already meets the required quantity (order quantity) I no longer need to look at any other stock, basically I need the new column to inform exactly the quantity that I will use from that stock to fulfill the order and bring the difference in the bottom line, until the order is completely fulfilled and so on. Once the order is fulfilled, it should show 0 in the lines of other stocks available for that order, meaning that we will not use them.

The order and customer column are repeated for each available stock, if I have an order of 1000 qt and I have 10 stocks, the order and customer line will be repeated 10 times, this information is handled in previous stages of the flow, where I already search for a key and bring what the possible stock would be.

The functionality should be like this:

Step 1 - If stock is greater than or equal to the order, you must use the order quantity (completed order)

Step 2 - If the stock is less than the order, you must use the stock quantity and generate a new order quantity to be used in the line below the same order. (Next step)

Step 3 - If the stock is less than the new order quantity, you must repeat step 2. (Next step)

Step 4 - If stock is equal to or greater than the order quantity, you must use the new order quantity (Finished order)

Step 5 - Look at the next order and repeat the steps.

Remembering that it should be based on the order and the customer

Math formula seemed like the ideal functionality at first, but it didn’t work because it had these rules.

8

Yes, but in this situation have no way to install the extension, so it needs to be something native.

9

Why not just build this as an iterative loop using just the native nodes? How many rows of data per run?

If you want some help building it out, then we would need some data. Do you have 1 table with the inventory and another table with the sale / stock adjustments? Can you upload files with sample data but correct column names and structure?

3 Likes
10

It can also work, I thought Java would be the most coherent alternative but other alternatives can work too.

The number of lines is variable, but can reach thousands.

11

I just built an much more complex iterative joining / processing loop that handles hundreds to thousands of iterations from the join table against a wide transaction table with 100k-500k of rows. It completes in 5 to 10 minutes. (I am running a pretty powerful PC though) I would expect your project to come in under that performance wise. If that processing time is a reasonable trade off for easy process auditing and editing then we could help you quickly build something out to test.

3 Likes
12

Okay!

What do you have in mind?

13

Upload an excel document to get us started.

Also, the input table appears to come in pre-grouped with the available stock counts at the time of order. If so, then the solution doesn’t have to be iterative (or even loop based at all). Does the calculated reduced stock info from above orders need to become the basis for future orders, or is that updated stock info already in the table for each order?

1 Like
14

The file has been uploaded.

There is no need for there to be new stock, as this table will update every day, meaning there is no need to “Update” the stock.

15

It doesn’t look like the file uploaded to the forum.

16

I’m sorry.

Use this similar archive.

STOCK_java.xlsx (17.7 KB)

2 Likes
17

Edit:

Sample Workflow.knwf (2.7 MB)

Here it is cleaned up with some annotations to explain how I calculated it. There is no group capability in the Moving Aggregation node at this point, so a group loop was required for this one. The loop should calculate pretty quickly with this minimal loop processing, so run time shouldn’t be an issue.

2 Likes
18

Have you given it a test run? I am curious as to the run time on your data using this native looping approach Vs your prior Python code.

19

Hello, sorry for the delay!

I couldn’t use it because when opening the flow it asks to install extensions.

On this computer I have some restrictions to install external sources, I can only use native nodes.

Error_extensios

20

Ok. I can swap those out for base nodes next time I have KNIME open.

1 Like
21

Sample Workflow (1).knwf (2.8 MB)

Try this one.