Icon

KNIME_​Goal_​Seek_​Demo

Goal Seek Demo
Demonstrate use of a recursive loop to perform a "goal seek". It is a proof of concept and there are some flaws such as "whathappens if we never get an answer that is "within tolerance" of the required answer. In which case the loop would continue until the end point is reached.A more efficient mechanism would be to "play golf", so that rather than simply iterating between low and high bounds, thealgorithm would see if the current answer was "too high" or "too low" compared with the previous iteration and then adjust thenext guess accordingly. Maybe that's a workflow for another day! ;-)This workflow was created to assist in forum question:https://forum.knime.com/t/excel-goal-seek-function-in-knime/320038th April 2021, @takbb Brian Bates Aim:Given the input data, the task is to adjust the value of"Volume" on row 1, until(Sum of [percentage of Volume]) / numberOfRows = 1.5 AppendPerc of VolAppend Perc of RevParametersReturn to base initial set of columnswith formulaePut parameters into variablesSplit out row one from the othersModify volume on row 1Bring the rows back togetherCalculate new total anddifference fromtargetIf within "tolerance" oftarget, set exitloop column to "true"change columns to variablesso they can bedisplayed andused by Loop EndLog current detailsfor this iterationNode 29Repeat untilexitloop="true"Bring aggregation valuesinto a single rowGet Count of RowsCalculate new Sum of Percentage of VolumeInput DataThe result Math Formula Math Formula Table Creator Column Filter Table Rowto Variable Row Splitter Math Formula Concatenate Math Formula Rule Engine Table Rowto Variable Java Edit Variable RecursiveLoop Start Recursive Loop End Joiner GroupBy GroupBy Table Creator Excel Writer Demonstrate use of a recursive loop to perform a "goal seek". It is a proof of concept and there are some flaws such as "whathappens if we never get an answer that is "within tolerance" of the required answer. In which case the loop would continue until the end point is reached.A more efficient mechanism would be to "play golf", so that rather than simply iterating between low and high bounds, thealgorithm would see if the current answer was "too high" or "too low" compared with the previous iteration and then adjust thenext guess accordingly. Maybe that's a workflow for another day! ;-)This workflow was created to assist in forum question:https://forum.knime.com/t/excel-goal-seek-function-in-knime/320038th April 2021, @takbb Brian Bates Aim:Given the input data, the task is to adjust the value of"Volume" on row 1, until(Sum of [percentage of Volume]) / numberOfRows = 1.5 AppendPerc of VolAppend Perc of RevParametersReturn to base initial set of columnswith formulaePut parameters into variablesSplit out row one from the othersModify volume on row 1Bring the rows back togetherCalculate new total anddifference fromtargetIf within "tolerance" oftarget, set exitloop column to "true"change columns to variablesso they can bedisplayed andused by Loop EndLog current detailsfor this iterationNode 29Repeat untilexitloop="true"Bring aggregation valuesinto a single rowGet Count of RowsCalculate new Sum of Percentage of VolumeInput DataThe resultMath Formula Math Formula Table Creator Column Filter Table Rowto Variable Row Splitter Math Formula Concatenate Math Formula Rule Engine Table Rowto Variable Java Edit Variable RecursiveLoop Start Recursive Loop End Joiner GroupBy GroupBy Table Creator Excel Writer

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: brian
Created at: 2021-04-08
On NodePit since: 2021-04-08
Last update: 2024-04-30
Created with KNIME version: v4.3.2
Tags: demo

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!