Icon

calculation of possible combinations - using recursive loop

calculation of possible combinations - using recursive loop - takbb

A workflow to calculate the set of combinations from a given input.

see forum post:
https://forum.knime.com/t/finding-all-possible-combinations-of-items-shipping-from-different-warehouses/68192

The recursive loop is needed here to ungroup the table columns one column at a time, from the one with the least elements first through to the one with the most.A feature of UnGroup is that ungrouping a single column causes the remaining columns to be repeated for each newly created rowSo by ungrouping one at a time, we gradually build up the full set of combinations [A] [B,C] [ D, E, F]becomesA [B,C] [ D, E, F]becomesA B [ D, E, F]A C [ D, E, F]becomesA B DA C DA B EA C EA B FA C Fso this is where we get our full set of possible combinations Input: OrderOutputall combinations for "none"thsose with "none" preferencethsose with specified preferenceList of wh preferences This may be better sourcedfrom a reference list, but it will do for nownot noneList of unique available WH typescomplete set ofoptionsfor all itemsNode 15Make the current rows whichare now grouped listsit intoa set ofcolumns that can be ungroupedGroup byoriginal keyto get lists ofprefs for each key, and alsothe number of prefswhich is important as we will want to "ungroup"later in ascending order of numberof prefsStamp oroginal rowidon the rowidKeep just the grouplist for eachoriginal rowid keyascending orderof number of prefsget column countungroupthe current columnusing the given column nameiterate so wewill ungroup thenext columnrecursively processthe tableset current column to 0and include it on data table as we need itin a recursive loop.flow variables are not retainedon each iteration, so weneed to store intermediate info (e.g. current column number)in the tablecol name as variableget column namefor this iterationremove the columnname column thatwe created in ColumnExpressionsadd 1 to the "current column" columnand feed back themodified tableto the next iterationadd to column countto get correctmax iterationsfor loopCreate a columnwhich is the iterationnumber using the currentrow indexremove thecolumn numbercolumn thatwas required foriterating the loopPivot on OriginalRowIdKeyso we get back the wh values for each rowrotate the table(cols become rows)RowId was created fromOriginalRowID Key (we made this at the start)so now turn RowId back intoOriginalRowIdKey columnmake the iterationnumber the rowidSort on iterationnumberto get the required outputMake the table readable againby renaming cols toIteration and WHwe will usethis as the original idrather than keeping track ofitem code and qty.We will join them back laterJoin back to original datausing OriginalRowIdKey and return the Item Code and QtycolumnsTidy UpI left in originalwarehouse preferencefor checking purposesbut this can be removedTable Creator Table Creator Cross Joiner Row Filter Row Filter Column Filter Row Filter DuplicateRow Filter Concatenate Column Filter Transpose GroupBy RowID Column Filter Sorter Extract TableDimension Ungroup Recursive Loop End RecursiveLoop Start Math Formula Table Rowto Variable Column Expressions Column Filter Math Formula Math Formula(Variable) Math Formula Column Filter Unpivoting Transpose RowID RowID Sorter Column Rename RowID Joiner Column Resorter The recursive loop is needed here to ungroup the table columns one column at a time, from the one with the least elements first through to the one with the most.A feature of UnGroup is that ungrouping a single column causes the remaining columns to be repeated for each newly created rowSo by ungrouping one at a time, we gradually build up the full set of combinations [A] [B,C] [ D, E, F]becomesA [B,C] [ D, E, F]becomesA B [ D, E, F]A C [ D, E, F]becomesA B DA C DA B EA C EA B FA C Fso this is where we get our full set of possible combinations Input: OrderOutputall combinations for "none"thsose with "none" preferencethsose with specified preferenceList of wh preferencesThis may be better sourcedfrom a reference list, but it will do for nownot noneList of unique available WH typescomplete set ofoptionsfor all itemsNode 15Make the current rows whichare now grouped listsit intoa set ofcolumns that can be ungroupedGroup byoriginal keyto get lists ofprefs for each key, and alsothe number of prefswhich is important as we will want to "ungroup"later in ascending order of numberof prefsStamp oroginal rowidon the rowidKeep just the grouplist for eachoriginal rowid keyascending orderof number of prefsget column countungroupthe current columnusing the given column nameiterate so wewill ungroup thenext columnrecursively processthe tableset current column to 0and include it on data table as we need itin a recursive loop.flow variables are not retainedon each iteration, so weneed to store intermediate info (e.g. current column number)in the tablecol name as variableget column namefor this iterationremove the columnname column thatwe created in ColumnExpressionsadd 1 to the "current column" columnand feed back themodified tableto the next iterationadd to column countto get correctmax iterationsfor loopCreate a columnwhich is the iterationnumber using the currentrow indexremove thecolumn numbercolumn thatwas required foriterating the loopPivot on OriginalRowIdKeyso we get back the wh values for each rowrotate the table(cols become rows)RowId was created fromOriginalRowID Key (we made this at the start)so now turn RowId back intoOriginalRowIdKey columnmake the iterationnumber the rowidSort on iterationnumberto get the required outputMake the table readable againby renaming cols toIteration and WHwe will usethis as the original idrather than keeping track ofitem code and qty.We will join them back laterJoin back to original datausing OriginalRowIdKey and return the Item Code and QtycolumnsTidy UpI left in originalwarehouse preferencefor checking purposesbut this can be removedTable Creator Table Creator Cross Joiner Row Filter Row Filter Column Filter Row Filter DuplicateRow Filter Concatenate Column Filter Transpose GroupBy RowID Column Filter Sorter Extract TableDimension Ungroup Recursive Loop End RecursiveLoop Start Math Formula Table Rowto Variable Column Expressions Column Filter Math Formula Math Formula(Variable) Math Formula Column Filter Unpivoting Transpose RowID RowID Sorter Column Rename RowID Joiner Column Resorter

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: dbodenmiller
Created at: 2023-06-05
On NodePit since: 2023-06-06
Last update: 2024-04-28
Created with KNIME version: v4.7.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!