Icon

Journal Division into Balanced sub pages

Journal Division into Balanced sub pages
Provides a means of terminating the java snippet if it gets into an infinite (or finite but verylarge) loop.There isn't a mechanism for externally cancelling the snippet when in operation, so itperiodically (every n cycles) checks for a value in a file "safetybreak.txt" in the workflowdata area.If it contains the "BREAK" at the beginning. it recursively exits all loops. JOURNAL PAGE SIZE should be set to themaximum number of entries to be assigned peroutput Page.CHUNK SIZE should be set to the pagesize unlessthis causes the java snippet to perform badly. If it isset lower, java will generally try to return smallersubsets and may therefore improve performance onlarger datasets, but the trade off is that pages maynot end up being as fully populated.Only set chunksize lower than pagesize if reallynecessary Recursive Loop within Journal - Collect a page of subsets on each iteration - Return remaining lines for re-analysis with each iteration Loop through All Journals,one journal at a time Rename the sub journals as a,b,c... Get Summary Totals for sub journals, and make into outputs for results spreadsheet Bring back any information that we didn't bring into the loop. Reset amounts back asdoubles Perform a pre-check that all supplied journals balance to zero.Produce error message if they don't Find balanced sub journals@takbb Brian Bates 18 Oct 2022 Defines location and variables forsafetybreak.txt fileand provides easymeans of locating filein workflow data foldergroup up the journalfor processingGet derived "subsets" ofamounts andpositions as rows(one subset per row)Fill down the blank entriesGenerate a "key"for each Subset sothat we can subsequently identifyall members of a subsetUngroup to expand out the Amount and rank subsetsso they are one entry per rowagain.This will also copy down thesubset keys so each canbe identifiedTurnPositions subset Stringinto a ListGenerate an identifierfor journal groupingsconsisting of journal numberand rank number.This is a potential "block"Get Counts and Sumsfor eachsubset blockOnly keep groupswhere Sum is zero(i.e. group balances)Descending order of"count" for each subset. Aswe want to retain the one with the highestcountRename counter asSubSetKeyRename Amountsand Positionback toAmount and rankto match originalnames usedby Java Snippet(we want this to be usable by recursive loop)Recursivelyprocesssubsets for thecurrent journalgo back and do anotherround with the currentjournal until all resolvedNode 44TurnAmounts subset Stringinto a ListKeep onlycolumns ofinterestGive each item an "id"within its journalMultiply by 100to turn valuesinto Integersto avoid Java's crazy rounding errorsGet rows that are usedby this journal groupFind the sets that can make it to the next roundKeep the firstrow found for each rankitem. This is then the set remainingto be tried againOutput of journal valuesCheck if anything leftto process. Set End flagfalse assomething to dobatch up the listof what's leftready to pass backto the java snippetSet End flagas falseJoin back to original datato bring through any missingcolumnsremovesuperflousblank thatmay creep in fromunpacking arrayRemove oldjournal name and"rank" positionJournal NoWe now have thebalancing setsfor each journal.Group them into page sizesMake Amounta Double againand divide by 100Remake rowidSpecify FinalJournal Max Pagesizeand a suggestedCHUNK SIZESet CHUNK SIZEto PAGE SIZEunless this doesn't give good performanceAny subsetsfound?No rows were foundIncrease the block sizePut variables intothe row so wepick up their newvalues on thenext iterationGet Block size variablefrom table. This allows formodification to be madeto block size with each iterationMake Block Size the Max ChunkSize fromuser settingsRemove BLOCK_SIZEcolumnWe will re-create itfrom current variableSet Block Completeas variable for later useKeep only requiredcolumnsAmount and rankto IntegerEnsure columnsmatch requiredoutput for looopuse iteration counteras "group balancing set" numberGet counts byjournal parts"journal balancing sets"The End(kind of)Node 154Aggregate totalrows foundMake sure therows will fit intothe journal page sizeAlways allow first row through. Otherwise wemay never find a "solution"if they won't fit into a pageand we'd loop indefintelyRelabel sets to a,b,c etcGet derived "subsets" ofamounts andpositions as rows(one subset per row)Generate a "key"for each Subset sothat we can subsequently identifyall members of a subsetRename counter asSubSetKey Write out the infowith summaryand checks Returns the subsets ofeach group set upto defined maximum size. Subsets will sum to zero.If this appears to be looping infinitely (theoretically possible on a large data set),manually execute the Emergency Stop nodeNode 176 Raw Journal File inMake columnname compatibleSort outdata typesSort byjournaland sub numberRetain columnsof interest for outputtidy column orderreplacerowidExclude "duplicate"rowsDoes nothing.Tidying data flowMakes convenient point forpicking up date flow for distributing to other nodesCompare withoriginal data setShould be matchesand nothing elseMake Amounta Double againand divide by 100Summariseby journaland subjournalRemove roundingerroron "Doubles"Round total to 2dpSummariseby journal"Subjournal"as "Total"Bring in thesubtotalfor each journalBy Journaland rowidSummariseby journal"Subjournal"as "Total"Bring in thesubtotalfor each journalSort KeySort KeySort KeyTidy uptidy column namesDoes it balance?Remove Zero sumsTurn into integersTurn into integers FAIL -Supplied Journalsdo not balanceIf this errors, view outputof previous nodeAll good if everythingbalances, otherwisebranch to display an error Executefrom hereSTOP COMMANDGO COMMAND !EMERGENCY STOP! Manually reset and execute this node to Interrupt theJava loops in event of an infinite loop condition Re-Executing this node allows the workflow to run after an "emergency stop"does nothing butassists with keepingworkflow tidy Reset and Execute this nodeto open Workflow Data Folderin Explorer,to find output spreadsheetOpen File or Folder GroupBy Ungroup Missing Value Counter Generation Ungroup Cell Splitter String Manipulation GroupBy Row Filter Sorter Column Rename Column Rename RecursiveLoop Start Recursive Loop End Group Loop Start Cell Splitter Column Filter Rank Math Formula Joiner Joiner DuplicateRow Filter Loop End Empty Table Switch End IF String Manipulation(Variable) GroupBy String Manipulation(Variable) Joiner Row Filter Column Filter Column Rename Column Resorter Math Formula RowID Variable Creator Pass-through Empty Table Switch Pass-through Math Formula(Variable) Pass-through CASE Switch End Variable toTable Column Table Rowto Variable Math Formula Column Filter Table Rowto Variable Column Filter String To Number Table Manipulator Math Formula GroupBy Joiner Row Filter Moving Aggregation Rule-basedRow Filter Java Snippet Ungroup Counter Generation Column Rename Excel Writer Java Snippet Row Filter Excel Reader Column Rename Table Manipulator Sorter Column Filter Column Resorter RowID Row Filter Pass-through Joiner Math Formula GroupBy Math Formula GroupBy String Manipulation Concatenate Sorter GroupBy String Manipulation Concatenate Math Formula Math Formula Math Formula Column Filter Column Rename GroupBy Row Filter Math Formula Math Formula Java Snippet Empty Table Switch CASE Switch End Pass-through Table Creator Table Creator CSV Writer CSV Writer Pass-through Open File or Folder Provides a means of terminating the java snippet if it gets into an infinite (or finite but verylarge) loop.There isn't a mechanism for externally cancelling the snippet when in operation, so itperiodically (every n cycles) checks for a value in a file "safetybreak.txt" in the workflowdata area.If it contains the "BREAK" at the beginning. it recursively exits all loops. JOURNAL PAGE SIZE should be set to themaximum number of entries to be assigned peroutput Page.CHUNK SIZE should be set to the pagesize unlessthis causes the java snippet to perform badly. If it isset lower, java will generally try to return smallersubsets and may therefore improve performance onlarger datasets, but the trade off is that pages maynot end up being as fully populated.Only set chunksize lower than pagesize if reallynecessary Recursive Loop within Journal - Collect a page of subsets on each iteration - Return remaining lines for re-analysis with each iteration Loop through All Journals,one journal at a time Rename the sub journals as a,b,c... Get Summary Totals for sub journals, and make into outputs for results spreadsheet Bring back any information that we didn't bring into the loop. Reset amounts back asdoubles Perform a pre-check that all supplied journals balance to zero.Produce error message if they don't Find balanced sub journals@takbb Brian Bates 18 Oct 2022 Defines location and variables forsafetybreak.txt fileand provides easymeans of locating filein workflow data foldergroup up the journalfor processingGet derived "subsets" ofamounts andpositions as rows(one subset per row)Fill down the blank entriesGenerate a "key"for each Subset sothat we can subsequently identifyall members of a subsetUngroup to expand out the Amount and rank subsetsso they are one entry per rowagain.This will also copy down thesubset keys so each canbe identifiedTurnPositions subset Stringinto a ListGenerate an identifierfor journal groupingsconsisting of journal numberand rank number.This is a potential "block"Get Counts and Sumsfor eachsubset blockOnly keep groupswhere Sum is zero(i.e. group balances)Descending order of"count" for each subset. Aswe want to retain the one with the highestcountRename counter asSubSetKeyRename Amountsand Positionback toAmount and rankto match originalnames usedby Java Snippet(we want this to be usable by recursive loop)Recursivelyprocesssubsets for thecurrent journalgo back and do anotherround with the currentjournal until all resolvedNode 44TurnAmounts subset Stringinto a ListKeep onlycolumns ofinterestGive each item an "id"within its journalMultiply by 100to turn valuesinto Integersto avoid Java's crazy rounding errorsGet rows that are usedby this journal groupFind the sets that can make it to the next roundKeep the firstrow found for each rankitem. This is then the set remainingto be tried againOutput of journal valuesCheck if anything leftto process. Set End flagfalse assomething to dobatch up the listof what's leftready to pass backto the java snippetSet End flagas falseJoin back to original datato bring through any missingcolumnsremovesuperflousblank thatmay creep in fromunpacking arrayRemove oldjournal name and"rank" positionJournal NoWe now have thebalancing setsfor each journal.Group them into page sizesMake Amounta Double againand divide by 100Remake rowidSpecify FinalJournal Max Pagesizeand a suggestedCHUNK SIZESet CHUNK SIZEto PAGE SIZEunless this doesn't give good performanceAny subsetsfound?No rows were foundIncrease the block sizePut variables intothe row so wepick up their newvalues on thenext iterationGet Block size variablefrom table. This allows formodification to be madeto block size with each iterationMake Block Size the Max ChunkSize fromuser settingsRemove BLOCK_SIZEcolumnWe will re-create itfrom current variableSet Block Completeas variable for later useKeep only requiredcolumnsAmount and rankto IntegerEnsure columnsmatch requiredoutput for looopuse iteration counteras "group balancing set" numberGet counts byjournal parts"journal balancing sets"The End(kind of)Node 154Aggregate totalrows foundMake sure therows will fit intothe journal page sizeAlways allow first row through. Otherwise wemay never find a "solution"if they won't fit into a pageand we'd loop indefintelyRelabel sets to a,b,c etcGet derived "subsets" ofamounts andpositions as rows(one subset per row)Generate a "key"for each Subset sothat we can subsequently identifyall members of a subsetRename counter asSubSetKey Write out the infowith summaryand checks Returns the subsets ofeach group set upto defined maximum size. Subsets will sum to zero.If this appears to be looping infinitely (theoretically possible on a large data set),manually execute the Emergency Stop nodeNode 176 Raw Journal File inMake columnname compatibleSort outdata typesSort byjournaland sub numberRetain columnsof interest for outputtidy column orderreplacerowidExclude "duplicate"rowsDoes nothing.Tidying data flowMakes convenient point forpicking up date flow for distributing to other nodesCompare withoriginal data setShould be matchesand nothing elseMake Amounta Double againand divide by 100Summariseby journaland subjournalRemove roundingerroron "Doubles"Round total to 2dpSummariseby journal"Subjournal"as "Total"Bring in thesubtotalfor each journalBy Journaland rowidSummariseby journal"Subjournal"as "Total"Bring in thesubtotalfor each journalSort KeySort KeySort KeyTidy uptidy column namesDoes it balance?Remove Zero sumsTurn into integersTurn into integers FAIL -Supplied Journalsdo not balanceIf this errors, view outputof previous nodeAll good if everythingbalances, otherwisebranch to display an error Executefrom hereSTOP COMMANDGO COMMAND !EMERGENCY STOP! Manually reset and execute this node to Interrupt theJava loops in event of an infinite loop condition Re-Executing this node allows the workflow to run after an "emergency stop"does nothing butassists with keepingworkflow tidy Reset and Execute this nodeto open Workflow Data Folderin Explorer,to find output spreadsheetOpen File or Folder GroupBy Ungroup Missing Value Counter Generation Ungroup Cell Splitter String Manipulation GroupBy Row Filter Sorter Column Rename Column Rename RecursiveLoop Start Recursive Loop End Group Loop Start Cell Splitter Column Filter Rank Math Formula Joiner Joiner DuplicateRow Filter Loop End Empty Table Switch End IF String Manipulation(Variable) GroupBy String Manipulation(Variable) Joiner Row Filter Column Filter Column Rename Column Resorter Math Formula RowID Variable Creator Pass-through Empty Table Switch Pass-through Math Formula(Variable) Pass-through CASE Switch End Variable toTable Column Table Rowto Variable Math Formula Column Filter Table Rowto Variable Column Filter String To Number Table Manipulator Math Formula GroupBy Joiner Row Filter Moving Aggregation Rule-basedRow Filter Java Snippet Ungroup Counter Generation Column Rename Excel Writer Java Snippet Row Filter Excel Reader Column Rename Table Manipulator Sorter Column Filter Column Resorter RowID Row Filter Pass-through Joiner Math Formula GroupBy Math Formula GroupBy String Manipulation Concatenate Sorter GroupBy String Manipulation Concatenate Math Formula Math Formula Math Formula Column Filter Column Rename GroupBy Row Filter Math Formula Math Formula Java Snippet Empty Table Switch CASE Switch End Pass-through Table Creator Table Creator CSV Writer CSV Writer Pass-through Open File or Folder

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: BATEB031
Created at: 2022-10-10
On NodePit since: 2022-10-19
Last update: 2024-04-27
Created with KNIME version: v4.6.3
Tags: Subset SumBalance to zeroJournal balanceRecursion

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

Try NodePit Runner!