Icon

Allocate txn to budget 2

This node gives the required output If you were dealing with dollars and cents,then in the Math Formula nodes youwould need to multiply by 100. This willgreatly increase the rows created by theOne Row to Many (by 100 times), sowhilst this solution will work for the statedexample, it remains to see how well thatwould scale with large "to the cent/penny" budgets and data volumes If budgets/costs were not dealing withwhole dollars, and had to include cents,then just as at the beginning, the valueswould have to be multiplied by 100 tomake integer values, at the end here theresultant cost and budget values wouldhave to be divided by the same factor. Summarise the cost/budget values.Note that at this point we useCOUNT and not SUM since we arecounting the individual "units" (1 row= 1 unit) Summarise the cost/budget values.Note that at this pointwe use COUNT andnot SUM since we arecounting theindividual "units" (1row = 1 unit) Summarise the total budgetused.Here we use SUM again, as theprevious group by has alreadysummarised back into "non-unit"values, and we are again dealingwith actual values Below - Java Snippet Version - assigns all costs to all possible purchase orders for a given WO, and thenuses java snippet to determine which to assign costs to and which to discard Above - brute force standard node version - it works but it wouldn't scale well with large numbers PO BudgetsTransactionsFor each smallest divisible "unit" of moneycreate a rowFor each smallest divisible "unit" of moneycreate a rowMatch individual "units" of budgets with "units" of costs, by WO NumberAllocated BudgetsTransactions exceeding available budget(Should be empty!)Remaining Budgetsavailableget a sequence numberfor transactionsRename totxn seqsort by txn seqTotal BudgetUsedtidy upRequired OutputRank rows within WO numberRank rows within WO numberConvert Budgetsto Integersfor One Row to ManyConvert Costto Integers for One Row to Manyget a sequence numberfor transactionsRename totxn seqPO BudgetsRename totxn seqTransactionsRename totxn seqget a sequence numberfor transactionsget a sequence numberfor transactionsJoin all Budgets to all transactionswith the same WO numberSort by budget and transactionCumulativelyassign txn toPO and mark rowsfor deletion where exhaustedRemove rowsmarked fordeletionTidy upTidy upTidy up Table Creator Table Creator One Row to Many One Row to Many Joiner GroupBy GroupBy GroupBy Counter Generation Column Rename Sorter GroupBy Column Filter Column Resorter Rank Rank Math Formula Math Formula Counter Generation Column Rename Table Creator Column Rename Table Creator Column Rename Counter Generation Counter Generation Joiner Sorter Java Snippet Row Filter Column Filter Column Resorter Column Rename This node gives the required output If you were dealing with dollars and cents,then in the Math Formula nodes youwould need to multiply by 100. This willgreatly increase the rows created by theOne Row to Many (by 100 times), sowhilst this solution will work for the statedexample, it remains to see how well thatwould scale with large "to the cent/penny" budgets and data volumes If budgets/costs were not dealing withwhole dollars, and had to include cents,then just as at the beginning, the valueswould have to be multiplied by 100 tomake integer values, at the end here theresultant cost and budget values wouldhave to be divided by the same factor. Summarise the cost/budget values.Note that at this point we useCOUNT and not SUM since we arecounting the individual "units" (1 row= 1 unit) Summarise the cost/budget values.Note that at this pointwe use COUNT andnot SUM since we arecounting theindividual "units" (1row = 1 unit) Summarise the total budgetused.Here we use SUM again, as theprevious group by has alreadysummarised back into "non-unit"values, and we are again dealingwith actual values Below - Java Snippet Version - assigns all costs to all possible purchase orders for a given WO, and thenuses java snippet to determine which to assign costs to and which to discard Above - brute force standard node version - it works but it wouldn't scale well with large numbers PO BudgetsTransactionsFor each smallest divisible "unit" of moneycreate a rowFor each smallest divisible "unit" of moneycreate a rowMatch individual "units" of budgets with "units" of costs, by WO NumberAllocated BudgetsTransactions exceeding available budget(Should be empty!)Remaining Budgetsavailableget a sequence numberfor transactionsRename totxn seqsort by txn seqTotal BudgetUsedtidy upRequired OutputRank rows within WO numberRank rows within WO numberConvert Budgetsto Integersfor One Row to ManyConvert Costto Integers for One Row to Manyget a sequence numberfor transactionsRename totxn seqPO BudgetsRename totxn seqTransactionsRename totxn seqget a sequence numberfor transactionsget a sequence numberfor transactionsJoin all Budgets to all transactionswith the same WO numberSort by budget and transactionCumulativelyassign txn toPO and mark rowsfor deletion where exhaustedRemove rowsmarked fordeletionTidy upTidy upTidy up Table Creator Table Creator One Row to Many One Row to Many Joiner GroupBy GroupBy GroupBy Counter Generation Column Rename Sorter GroupBy Column Filter Column Resorter Rank Rank Math Formula Math Formula Counter Generation Column Rename Table Creator Column Rename Table Creator Column Rename Counter Generation Counter Generation Joiner Sorter Java Snippet Row Filter Column Filter Column Resorter Column Rename

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-07-25
On NodePit since: 2022-02-06
Last update: 2024-04-28
Created with KNIME version: v4.4.0

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

Try NodePit Runner!