Icon

Logistics _​ Stock Forecast (nested-recursive equations)

Logistics _ Stock Forecast (nested-recursive equations)

Logistics _ Stock Forecast (nested-recursive equations)


Workflow created for the KNIME forum ...

https://forum.knime.com/t/how-to-create-a-loop-between-to-equations-using-ones-input-as-the-others-output/39741

The challenge is to calculate a system of two consecutive ecuations (described in the Forum's post) f(1) 'Quantities to be ordered' and f(2) 'Stock Projections', this is applied for a multi 'Item' data set. These calculations have to be ran in a row by row basis (ordered by date), being the resulting f(2) 'Stock Projection (n+1)' inputed in next row at column 'Input Stock', being an input parameter in the next iteration' f(1) calculation.

Regarding the challenge itself, the easiest would be to code it (R?). However we test here that a possible approach by using just KNIME nodes is doable. The selected approach is a nested recursive loop handling the f(1) 'Quantities to be ordered' and f(2) 'Stock Projections' calculations, within an standard loop operating over the items (Ships, Banana, Apples…)


COMMENTS:
The provided data comes in an unstructured format; A first section is required to gather and transform these tables into a structured object. The resulting data frame can be considered the starting of the task proposed in the caption.

The collected data at final loop output, results in one month shorter in the time series than the starting data; this is due because we don't have a value to input in last month 'Safety Stock (n+1)'


For any comment or bug realted to the workflow, please do not hesitate to leave your comment.


DISCLAIMER:
The sharing of knowledge in KNIME Hub by using these examples and models, have only demonstration purposes in the advantage of the KNIME community.
These are meant to clarify theoretical background of mentioned subjects in caption.
I will not be held responsibility for any damages arising from the use of these models in your investment/valuation related work, without taking formal advise.

KNIME Hub Caption: Logistic _ Stock Projections (nested-recursive equations)Collect and Transform DATA from Repository: KNIME Forum challenge: https://forum.knime.com/t/how-to-create-a-loop-between-to-equations-using-ones-input-as-the-others-output/39741How to create a loop between two equations, using one's input as the other's output: Main Loop[item]Start: Main Loop [item]End: Data Gather from different EXCEL tabs: Data Gather and Standarization from 'Input - Stock' Data at Initial Month: Read Tabs From Provided Excel Data: DATA: DESCRIPTION: The challenge is to calculate a system of two consecutive ecuations (described in the Forum's post) f(1) 'Quantities to be ordered' and f(2) 'Stock Projections', this is applied for a multi 'Item' data set. These calculations have to be ranin a row by row basis (ordered by date), being the resulting f(2) 'Stock Projection (n+1)' inputed in next row at column 'Input Stock', being an input parameter in the next iteration' f(1) calculation. The selected approach is a nested recursive loop handling the f(1) 'Quantities to be ordered' and f(2) 'Stock Projections' calculations, within an standard loop operating over the items (Ships, Banana, Apples…)The provided data comes in an unstructured format; A first section is required to gather and transform these tables into a structured object. The resulting data frame can be considered the starting of the task proposed in the caption. COMMENT:The collected data at final loop output, results in one month shorter in the time series than the starting data; this is due because we don't have a value to input in last month 'Safety Stock (n+1)' NOTE: The provided data comes in an unstructured format; A first section is required to gather and transform these tables into a structured object. The resulting data frame canbe considered the starting of the task proposed in caption. NestedRecursive LoopEnd: equation 2: equation 1: NestedRecursive LoopStart: Nouveau Feuille de calcul Microsoft Excel.xlsxDATANouveau Feuille de calcul Microsoft Excel.xlsxSHEET NAMESPivot ItemsSplittUpper: Rank = 1 [ to Data Collect port ]Lower: Rank <> 1 (>1) [ to pass back port ]... to VariableNouveau Feuille de calcul Microsoft Excel.xlsxDATAInclude Row 3:Input - Stock (t=February)Exclude Row 3:Quanitites to be orderedStock Projectioninput - Safety Stockinput - Sales forecastIn-put - Up comming shipmentsStart LoopUnpivotColumn NamesCollectDataFormat to WIDEItem Loop StartNode 328First Month(Min. Month)Assign First Monthto 'Input - Stock'Repeat Looped DataTransformations to NormalizeALL Providedand NormalizedDATAKeep TRUES$${SItem}$$regex match approachto any position in $ColumnNames$LOGICLag safety stock (n+1)Step 1Lag safety stock (n+1)Step 2Fix Missed Values == 0revision (not needed)Quantities to be ordered ==$safety stock (n+1)$ + $input - Sales forecast$ -$In-put - Up comming shipments$ -$Input - Stock$Stock projections (n+1) ==$Input - Stock$ + $In-put - Up comming shipments$ + $Quantities to be ordered$ -$input - Sales forecast$Recursively Loops overReturned 'Passed Back' DataCollect only last iteration from first in-portSecond in-port is passed back to the loop startRankMonths ColumnINNER JOIN rank == 1with EquationResultsLagStock projections (n+1)(-1)Assign Current Recursive'Stock projections (n+1)'to next month position [rank == 2]in $Input - Stock$INNER JOIN rank > 1rank$rank$ - 1Clean UpMax*(rank)... to Variable(cotrol Loop Iterations)(cotrol Loop Iterations)$${IMax*(rank)}$$ - 1as Last Month miss 'safety stock (n+1)'Format to LONGReset RowIDRestore 'Item_' to Column NamesCollect all 'ITEM 'DataUpper: ItemsLower: TimeAppend TimeThree Componentsyyyy-MM-ddUpper == 3 componentsLower == 2 componentsAdress Tabto ItemUnpivotColumn NamesAdress Tabto Item in Column NamesStandardize Column Names for Recursive Loop Operations Excel Reader Read ExcelSheet Names Pivoting Row Splitter Table Rowto Variable Excel Reader Row Filter Row Filter Table Row ToVariable Loop Start Unpivoting Loop End Pivoting Table Row ToVariable Loop Start Java EditVariable (simple) GroupBy Cross Joiner Pivoting Concatenate Row Filter String Manipulation Moving Aggregation Math Formula Missing Value Math Formula Math Formula RecursiveLoop Start Recursive Loop End Rank Joiner Lag Column Rule Engine Joiner Math Formula Column Filter GroupBy Table Rowto Variable Java EditVariable (simple) Unpivoting RowID String Manipulation Loop End Column Splitter Column Appender String toDate Format Date FormatSplitter String Manipulation Unpivoting String Manipulation Handle Column Names KNIME Hub Caption: Logistic _ Stock Projections (nested-recursive equations)Collect and Transform DATA from Repository: KNIME Forum challenge: https://forum.knime.com/t/how-to-create-a-loop-between-to-equations-using-ones-input-as-the-others-output/39741How to create a loop between two equations, using one's input as the other's output: Main Loop[item]Start: Main Loop [item]End: Data Gather from different EXCEL tabs: Data Gather and Standarization from 'Input - Stock' Data at Initial Month: Read Tabs From Provided Excel Data: DATA: DESCRIPTION: The challenge is to calculate a system of two consecutive ecuations (described in the Forum's post) f(1) 'Quantities to be ordered' and f(2) 'Stock Projections', this is applied for a multi 'Item' data set. These calculations have to be ranin a row by row basis (ordered by date), being the resulting f(2) 'Stock Projection (n+1)' inputed in next row at column 'Input Stock', being an input parameter in the next iteration' f(1) calculation. The selected approach is a nested recursive loop handling the f(1) 'Quantities to be ordered' and f(2) 'Stock Projections' calculations, within an standard loop operating over the items (Ships, Banana, Apples…)The provided data comes in an unstructured format; A first section is required to gather and transform these tables into a structured object. The resulting data frame can be considered the starting of the task proposed in the caption. COMMENT:The collected data at final loop output, results in one month shorter in the time series than the starting data; this is due because we don't have a value to input in last month 'Safety Stock (n+1)' NOTE: The provided data comes in an unstructured format; A first section is required to gather and transform these tables into a structured object. The resulting data frame canbe considered the starting of the task proposed in caption. NestedRecursive LoopEnd: equation 2: equation 1: NestedRecursive LoopStart: Nouveau Feuille de calcul Microsoft Excel.xlsxDATANouveau Feuille de calcul Microsoft Excel.xlsxSHEET NAMESPivot ItemsSplittUpper: Rank = 1 [ to Data Collect port ]Lower: Rank <> 1 (>1) [ to pass back port ]... to VariableNouveau Feuille de calcul Microsoft Excel.xlsxDATAInclude Row 3:Input - Stock (t=February)Exclude Row 3:Quanitites to be orderedStock Projectioninput - Safety Stockinput - Sales forecastIn-put - Up comming shipmentsStart LoopUnpivotColumn NamesCollectDataFormat to WIDEItem Loop StartNode 328First Month(Min. Month)Assign First Monthto 'Input - Stock'Repeat Looped DataTransformations to NormalizeALL Providedand NormalizedDATAKeep TRUES$${SItem}$$regex match approachto any position in $ColumnNames$LOGICLag safety stock (n+1)Step 1Lag safety stock (n+1)Step 2Fix Missed Values == 0revision (not needed)Quantities to be ordered ==$safety stock (n+1)$ + $input - Sales forecast$ -$In-put - Up comming shipments$ -$Input - Stock$Stock projections (n+1) ==$Input - Stock$ + $In-put - Up comming shipments$ + $Quantities to be ordered$ -$input - Sales forecast$Recursively Loops overReturned 'Passed Back' DataCollect only last iteration from first in-portSecond in-port is passed back to the loop startRankMonths ColumnINNER JOIN rank == 1with EquationResultsLagStock projections (n+1)(-1)Assign Current Recursive'Stock projections (n+1)'to next month position [rank == 2]in $Input - Stock$INNER JOIN rank > 1rank$rank$ - 1Clean UpMax*(rank)... to Variable(cotrol Loop Iterations)(cotrol Loop Iterations)$${IMax*(rank)}$$ - 1as Last Month miss 'safety stock (n+1)'Format to LONGReset RowIDRestore 'Item_' to Column NamesCollect all 'ITEM 'DataUpper: ItemsLower: TimeAppend TimeThree Componentsyyyy-MM-ddUpper == 3 componentsLower == 2 componentsAdress Tabto ItemUnpivotColumn NamesAdress Tabto Item in Column NamesStandardize Column Names for Recursive Loop Operations Excel Reader Read ExcelSheet Names Pivoting Row Splitter Table Rowto Variable Excel Reader Row Filter Row Filter Table Row ToVariable Loop Start Unpivoting Loop End Pivoting Table Row ToVariable Loop Start Java EditVariable (simple) GroupBy Cross Joiner Pivoting Concatenate Row Filter String Manipulation Moving Aggregation Math Formula Missing Value Math Formula Math Formula RecursiveLoop Start Recursive Loop End Rank Joiner Lag Column Rule Engine Joiner Math Formula Column Filter GroupBy Table Rowto Variable Java EditVariable (simple) Unpivoting RowID String Manipulation Loop End Column Splitter Column Appender String toDate Format Date FormatSplitter String Manipulation Unpivoting String Manipulation Handle Column Names

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: RE90273
Created at: 2022-02-12
On NodePit since: 2022-02-18
Last update: 2024-04-27
Created with KNIME version: v4.4.2

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

Try NodePit Runner!