Complex multi row manipulation

Hi!

I have a use case where I'm generating inventory aging, and I need to do some complex multi row manipulation I cannot figure out.

What I'm trying to do is to split values from quantity into new rows every time there is a new delivery. So let's take a practical example from the screenshot below.

At 2005-06-05 I had 113 quantity in stock, and 2005-06-06 I got a resupply and now I have 189 in stock. What I want to do here is to take the delta between those two days into a new row so I can continue calculating the stock age for that specific stock quantity. In this case it would be 76. I then want every single minus in quantity going forward to substract from the 113 before starting to substract from the new 76.

Any smart people that can help me here?

Transformation

Accepted answers

Yoshiro_Fujimori

@Trailduck ,

It depends on the definition of "age".

If you want to get the days from the In-Date to a specific date, you can just add a formula tool as below.

Workflow added

Output

If you have any other definition, you would need different approach.

But as long as you have the data for each item, it should not be difficult.

ManageInventory.yxmd

All comments

caltang

I am assuming your first three columns are unique...

Use a Multi-Row Tool and Group by the three columns you have first.

Second, for a Delta to be present, what you can do is the following:

IF quantity = [quantity + 1]

THEN 0

ELSE [quantity + 1] - quantity

ENDIF

Choose the data type to Int16 as well.

Try it and see.

Hope it helps.

-Cal

Trailduck

Maybe I didn't explain correctly. I've also added some more columns to make it understandable.

Every time there is a new delivery, I want the salesAmount to be substracted from the previous stockQuantity until it reaches 0 before starting to substract from the new stockQuantity. So it should substract all salesAmount from 113 until it reaches 0, then it should start substracting from 76.

Yoshiro_Fujimori

Hi @Trailduck ,

I assume you use your stock FIFO (First In First Out), and you want to know the number of remaining items by each entry date.

If so, I would assign ID to each item in the stock, and add the date when it was input and output.

Input

Put only the date when there is a movement (In or Out).

Output

Workflow

I hope this works for your requirement.

ManageInventory.yxmd

Trailduck

Getting closer to something here, but what I'm trying to do is to calculate the age for the stock.

Trailduck

Getting closer to something here, but what I'm trying to do is to calculate the age for the stock.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703