Make a number series incremental

Hi Fellow users,

I’m trying to convert a number series into an increasing one. Example data are as below:

|Date | Origin Data | Desired Result | |
|2023-07-01 | 1 | 1 | |
|2023-07-02 | 2 | 2 | |
|2023-07-03 | 5 | 5 | |
|2023-07-04 | 8 | 8 | |
|2023-07-05 | 10 | 10 | |
|2023-07-06 | 14 | 10 | |
|2023-07-07 | 12 | 10 | |
|2023-07-08 | 11 | 10 | |
|2023-07-09 | 10 | 10 | |
|2023-07-10 | 13 | 13 | |
|2023-07-11 | 15 | 15 | |

In excel i can use a IF function to handle it, so I tried “lag column” node to compare values in two row and get the smaller one. But soon I realize it did not work since this comparision is dynamic in excel across whole sheet, and “lag column” is a static one.

So I guess I need a loop function, step from the largest date(and also the “should be” largest data), comparing 2 row at a time. Then move it all the way down to finish the task.

Anyone have idea on this?

Do you mind explaining the logic in a bit more detail? Been trying to work out why for 2023-07-05 to 2023-07-09 the desired result is 10 in all cases…

From what I read you are already thinking into the right direction - another alternative could be column expressions node with multi-row access enabled:

You can then access - in this example - the next three rows forward/backward.

E.g. if you wanted to look two rows ahead:

But (at least for me) more details are needed to potentially build a prototype - also would appreciate an example data (anonymised) in excel…

Thank you for helping on this Martin~

About “2023-07-05 to 2023-07-09 the desired result is 10 in all cases”
This is a supply chain case that try to optimize the supply, and data here is a cumlative supply of a material. So decreasing number means “someone take the material away” which did not make sense.

So my purpose is to make this series monotonically increasing, by decreasing those days has lower value than future day supply.

In this case, the “bottleneck” will be on 2023-07-09, which is lower than the previous Jul 6 - Jul 8 value, so the logic is:
Starting from the last day, compare value(today) with value(yesterday)
if value(today)< value(yesterday)
value(yesterday) = value(today)
and all the way down.

Not sure if I express it clearly.

I see that makes it clearer. And let’s say when you change yesterday to todays value and then move “up” one row, (so in previous iteration yesterday is today in the next iteration) so the consider the “new” value already

E.g.

|2023-07-07 | 12 | 10 | | Change to 10…
|2023-07-08 | 11 | 10 | | Changed to 10, Today 10 < Yesterday 12 => change Yesterday to 10
|2023-07-09 | 10 | 10 | | Stays 10, Today 10 < Yesterday 11 => change Yesterday to 10
|2023-07-10 | 13 | 13 | | Stays 13, Today 13 > Yesterday 10 => no change
|2023-07-11 | 15 | 15 | | Today 15 > Yesterday 13 => no change

I think I managed to sort it out:
SCM_Stock_Setting.knwf (149.6 KB)

You were right in that you needed a (recursive) loop to do this so for huge data sets it may take a while…

I used the deprecated Row Splitter as the new one does not allow to “chop off” the top two rows…

3 Likes

Just tried you flow, really elegant and it works well.
Many Thanks!!!

I would not try to use the loop end as concatenate may need to learn on loop well on it’s dynamics.

1 Like

Glad to hear! If it worked I’d appreciate to flag it as solution :-).

Yes recursion can be a bit difficult at first, but once you get the hang of it…

2 Likes

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