# Loop and aggregate values until a certain condition is met

**URL:** <https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045>\
**Category:** KNIME Analytics Platform\
**Created:** [August 13, 2024, 5:43am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045 "2024-08-13T05:43:09Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![kowisoft](https://forum-cdn.knime.com/user_avatar/forum.knime.com/kowisoft/32/3295_2.png) [@kowisoft](https://forum.knime.com/u/kowisoft)\
**Post date:** [August 13, 2024, 5:43am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/1 "2024-08-13T05:43:09Z")

</div>

Dear KNIMErs,

I can’t wrap my head around how to solve this little problem:

I have the following table:

![image](https://forum-cdn.knime.com/uploads/default/original/3X/8/1/816e48b10054b5b9178eab02d76a68906266df24.png)

What I basically want to do is to loop through these line items and stop, when a certain condition is met.

The condition is this

> IF item supplier X’s total value exceeds 100,000

I considered a loop and doing a Variable Condition Loop End Node, following the approach that @rs1 described in this workflow on the Community Hub:

> **[Loop on Condition – rs1](https://hub.knime.com/rs1/spaces/Public/KNIMEPress/KNIME_Advanced_Luck_4.5_2022011/AdvancedLuck/Chapter7/7.%20Loop%20on%20Condition~RbI0TmPcbK6ExCgI/current-state)**
>
> This workflow implements a loop cycle on condition, that is a loop that stops when a given condition is met. It is a game. I think of a number and write it int…

However, I can’t get the inner part of the loop to be working. I already played around with the GroupBy Node as well as the Moving Aggregator Node but they do not help me inside the loop (or I use them the wrong way, which is probably more realistic 😄 )

I have provided a (pretty basic) example workflow where I also have a Double Widget Node to interactively set the threshold value (the 100,000 from the IF statement above).

> **[Loop aggregate based on condition – kowisoft](https://hub.knime.com/kowisoft/spaces/Public/Loop%20aggregate%20based%20on%20condition~0AAJ5X9LDagUzBc7/current-state)**
>
> Solutions for data science: find workflows, nodes and components, and collaborate in spaces.

Can anyone nudge me in the right direction?

---

<div class="post-metadata">

**Author:** ![ajit555](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ajit555/32/178_2.png) [@ajit555](https://forum.knime.com/u/ajit555)\
**Post date:** [August 13, 2024, 7:19am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/2 "2024-08-13T07:19:59Z")

</div>

> [@kowisoft](#):
>
> IF item supplier X’s total value exceeds 100,000

Thanks for posting your workflow with sample data. It helps.

- Do you want to just filter your table without aggregating it?
- Assuming you want to just filter the table with the condition “IF item supplier X’s total value exceeds 100,000” the steps are:
  - Filter the table for “item supplier = X” (You can use a widget to switch the supplier name)
  - Create a column for cumulative sum of value
  - Filter the table for the sum value threshold (You can use a widget to change sum value threshold)
  - Once the able is filtered, you can derive additional statistics like list / count / frequency / min / max of item names etc.

---

<div class="post-metadata">

**Author:** ![kowisoft](https://forum-cdn.knime.com/user_avatar/forum.knime.com/kowisoft/32/3295_2.png) [@kowisoft](https://forum.knime.com/u/kowisoft)\
**Post date:** [August 13, 2024, 8:43am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/3 "2024-08-13T08:43:32Z")

</div>

Thank you for your response @ajit555

What I basically want to do is to assign the “values” to X until their threshold (100,000 in this example) is met, then assign the rest of the “values” to the other supplier, Y in this case. Line items details however should be maintained.

---

<div class="post-metadata">

**Author:** ![ajit555](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ajit555/32/178_2.png) [@ajit555](https://forum.knime.com/u/ajit555)\
**Post date:** [August 13, 2024, 10:40am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/4 "2024-08-13T10:40:06Z")

</div>

Could you please paste the desired output table?

Based on your above feedback, I am understanding that from Row0 till Row4, the “item supplier” column would be “X” (as the cumumulative sum value till this row is 100,000) and for rest of the rows, it would be “Y”.

Also, the above item supplier column would be a new one or the existing column needs to be updated?

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [August 13, 2024, 10:50am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/5 "2024-08-13T10:50:39Z")

</div>

@kowisoft without having looked deeply into it you will have to collect what is happening and store the intermediate values. I constructed this example but there is also the recursive loop that can somehow do that automatically but I am not that comfortable with.

> [@Stop my loop if the column sum is greater than 50% of my flow variable](https://forum.knime.com/t/stop-my-loop-if-the-column-sum-is-greater-than-50-of-my-flow-variable/48262/6):
>
> @kowisoft it could look something like this. Collection the iterating values in a temporary file and appending that at each step of the loop. And then like you have already done collect the sum of the column and then make a decision to sop (or not). Maybe not the most elegant way… Just make sure to reset the temporary file. One could also automate that by using a switch catching the first iteration of the loop (I have and example of that somewhere).

> [@Stop my loop if the column sum is greater than 50% of my flow variable](https://forum.knime.com/t/stop-my-loop-if-the-column-sum-is-greater-than-50-of-my-flow-variable/48262/12):
>
> @kowisoft in the end you will have to judge how you want to solve your problems slight_smile and if you are happy with it we are all happy. I would like to add these remarks: your loop would as far as I see it not iterate over the lines but would fill up the loop at each step so the number of lines processed would always get larger. If you only have small amounts of data this might not be an issue (and if this is what you want) then as far as I understood your request you wanted to process t…

Question is do you want some sort of optimization where you would try to come as close to the 100k as possible with your values.

---

<div class="post-metadata">

**Author:** ![MartinDDDD](https://forum-cdn.knime.com/user_avatar/forum.knime.com/martindddd/32/69988_2.png) [@MartinDDDD](https://forum.knime.com/u/MartinDDDD)\
**Post date:** [August 13, 2024, 11:44am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/6 "2024-08-13T11:44:45Z")

</div>

> [@kowisoft](#):
>
> What I basically want to do is to loop through these line items and stop, when a certain condition is met.

Is the objective to find the item either before or after this threshold for Supplier X is met?

Do you want to apply this to any of the suppliers of items in the table or do you just want to find “the first” that exceeds the threshold?

Depending on this a solution may look a little different.

I’ve created a prototype with four scenarios:

1. finding all items for each supplier once threshold is exceeded
2. finding all items for each supplier before threshold is exceeded
3. finding the exact item for each supplier that crosses over the threshold.

Workflow:  
[Loop aggregate based on condition\_MartinDDDD.knwf](https://forum.knime.com/uploads/short-url/dWsgh3fc7Iik5wmoQRNlWvQmvos.knwf) (118.5 KB)

Overview:

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/e/0/e09ffbed1a782fe43c4e4c5602925ccbad329117.png)

---

<div class="post-metadata">

**Author:** ![ajit555](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ajit555/32/178_2.png) [@ajit555](https://forum.knime.com/u/ajit555)\
**Post date:** [August 13, 2024, 4:30pm UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/7 "2024-08-13T16:30:09Z")

</div>

I was facing a similar allocation problem and posted here

> [@Allocate projects to suppliers subject to overall threshold project aggregate value per supplier](https://forum.knime.com/t/allocate-projects-to-suppliers-subject-to-overall-threshold-project-aggregate-value-per-supplier/82065):
>
> Hello, Need your help in solving the below problem: A company has 50 projects. Each project has a project amount. Company has a list of suppliers. For any given supplier, the company has a threshold limit on the overall project amount that the supplier can handle called ‘Threshold’. The total amount of projects that a supplier is associated should be less than the threshold value. The goal is to allocate the projects to suppliers. The number of projects can increase or decrease as well as the…

Please let me know if it is similar to yours?

---

<div class="post-metadata">

**Author:** ![kowisoft](https://forum-cdn.knime.com/user_avatar/forum.knime.com/kowisoft/32/3295_2.png) [@kowisoft](https://forum.knime.com/u/kowisoft)\
**Post date:** [August 14, 2024, 8:52am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/8 "2024-08-14T08:52:33Z")

</div>

Thank you for providing a link to that post @ajit555

If I get it right, it means that the input ports of the Recursive Loop Node in that example do the following:

- 
  1. Input Port: takes the ‘current batch’ aka the current supplier that is assigned to the project (sub)list

- 
  1. Input Port: takes the remaining projects, where no supplier is yet assigned

- 
  1. Input Port: takes the list of the remaining suppliers, who have not yet been assigned to a project

Is that correct?

Also big THANK YOU to @mlauber71 for reminding me that I have asked a similar question some time ago. Sorry for ‘double posting’ (kind of), I knew I had this issue before in a pretty similar use case but didn’t scroll ‘far enough’ to find that old post of mine.

---

<div class="post-metadata">

**Author:** ![MartinDDDD](https://forum-cdn.knime.com/user_avatar/forum.knime.com/martindddd/32/69988_2.png) [@MartinDDDD](https://forum.knime.com/u/MartinDDDD)\
**Post date:** [August 14, 2024, 10:23am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/9 "2024-08-14T10:23:58Z")

</div>

I take you are referring to the example provided in the other topic - let me try and explain:

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/6/069758f7822f325fa9120cc21da6ff492d2a5635.png)

1. always passes through the projects table - on first iteration in full, in any subsequent iteration it is passing through “what is left over” (bottom port of 6)
2. Always passes through the suppliers table - on first iteration in full, in any subsequent iteration only the remaining suppliers (bottom port of 7)
3. Collection output port - collects those projects enriched with an allocated supplier, where the cumultative value was below the threshold (top port of 6)
4. recursion port for projects table - passes those projects that came after the cumultative threshold was met back to the beginning of the loop for the next iteration. Important: Table columns need to match the initial input 1:1 - that’s why the cumultative sum column gets removed before passing the table back
5. recursion port for suppliers table - passes through the remaining suppliers to be processed (bottom port of 7)

Hope that makes sense…

---

<div class="post-metadata">

**Author:** ![kowisoft](https://forum-cdn.knime.com/user_avatar/forum.knime.com/kowisoft/32/3295_2.png) [@kowisoft](https://forum.knime.com/u/kowisoft)\
**Post date:** [August 14, 2024, 11:39am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/10 "2024-08-14T11:39:15Z")

</div>

Thank you for your explanation @MartinDDDD - this made it a lot clearer.

I have provided my ‘final’ dummy workflow below:

> **[supplierAllocation ks – kowisoft](https://hub.knime.com/kowisoft/spaces/Public/supplierAllocation%20ks~8-zs9AjOfASoTvfd/current-state)**
>
> Solutions for data science: find workflows, nodes and components, and collaborate in spaces.

Some caveats in my use case that required a (slightly) different approach:

- I had some ‘required’ vendors / suppliers in my list, e. g. for products that can only be sourced from those suppliers. Therefore I was using a Row Splitter to split up the original table before I start the Recursive Loop. The basic idea is to separate these ‘already assigned’ line items to a separate branch and merge them back together after the loop

- Because of these ‘required’ suppliers I also deducted the ‘already assigned’ volume from these required line items from the threshold value (driven by the Double Widget Node) like so → see screenshot below. Important: I only do this for Supplier 1 (S1) because I only have 2 suppliers → see also next bullet point  
 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/a/0aaba526fecd01561691e221b23bd0f3da343c96.png)

- I only have two suppliers (in this example and in my real world use case). As a result of this I limited the **Maximum Number of Iterations** to **1**. This basically says…

```auto
Assign line items up to the (adjusted) volume to S1, the rest stays unassigned

```

- Because I only have 2 suppliers, I also made sure that I have 2 collector ports (compared to 2 recursion ports) for the Recursive Loop End Node so I get the items that were assigned S1 at output port 1 and the ‘remainder’ at output port 2

Then it’s just a question of conditional logic (Rule Engine Node) and a little bit of concatenation to bring it all back together

At the end, I have just a simple GroupBy (not needed in my real world use case) to test if the workflow delivers the results as expected.

Thanks a lot for all your help in this thread @MartinDDDD , @ajit555 and @mlauber71 - I tested this now with all kinds of different values and it works and seems to be robust enough.

The one thing that remains for me now is to apply this to my real world use case, where I work more with variables 😄

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [November 12, 2024, 11:40am UTC](https://forum.knime.com/t/loop-and-aggregate-values-until-a-certain-condition-is-met/82045/11 "2024-11-12T11:40:03Z")

</div>

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