Icon

GroupBy-alternative with regex join component

The magic pattern that turns a single column into multiple columns based ongroupings Lower flow show-cases an alternative solution which replaces theunintuitive complexity that achieves the regex-matching with a single "JoinRegexp_Like component Make regex patternEverything in place,the rows are groupedby unique group idReady for the magic?Top is the lines wherethe marker matched.Bottom is lines where marker didn't matchThe top port will contain NO DUPLICATESThe lower port needs to haveDUPLICATES REMOVEDWe need an idthat we can sort onto keep things in theright orderafter we've done some cleaning up. We alsoneed to spot the duplicates created by the cross joiner weare about to useRemove duplicates(keep first)One big family againOne happy familyagainUse RIGHT JOIN Remove rows containingkeywords. They should only bein the top, and will be here only wherecrossjoiner put them against the "wrong" keywordClose your eyes...Slight of hand...Now open your eyes...Pull the curtain roundWhich of my peoplegot on withyour people?cumulatively renumber the groupmarkersDiscard anyinitial"unloved" rowsat the beginningRemove unwantedcolumnsKeywordsMy people will talkto your people..!Keywords COMPONENTAllows join on regular expressionLeft Join + Actual Joinoutput on same portto bring together full setof rowsClose your eyes...Slight of hand...Discard anyinitial"unloved" rowsat the beginningRemove unwantedcolumnsNow open your eyes...Pull the curtain roundOne happy familyagainFill Grouping IDs downwardsthis does what the moving aggregation would havedone when filling in the group numbers.But this time we used Math Formulato provide the group numbering, so we need todo this part ourselvesCounter Generationplus rename(saves on a node!)String Manipulation Column Rename Row Splitter Counter Generation DuplicateRow Filter Concatenate Sorter Joiner GroupBy Transpose Ungroup Column Filter Rule Engine Moving Aggregation Row Filter Column Filter Extract Data Tablefrom Delimited Text Cross Joiner Extract Data Tablefrom Delimited Text Join Regexp_Like Joiner GroupBy Transpose Row Filter Column Filter Ungroup Column Filter Sorter Missing Value Math Formula The magic pattern that turns a single column into multiple columns based ongroupings Lower flow show-cases an alternative solution which replaces theunintuitive complexity that achieves the regex-matching with a single "JoinRegexp_Like component Make regex patternEverything in place,the rows are groupedby unique group idReady for the magic?Top is the lines wherethe marker matched.Bottom is lines where marker didn't matchThe top port will contain NO DUPLICATESThe lower port needs to haveDUPLICATES REMOVEDWe need an idthat we can sort onto keep things in theright orderafter we've done some cleaning up. We alsoneed to spot the duplicates created by the cross joiner weare about to useRemove duplicates(keep first)One big family againOne happy familyagainUse RIGHT JOIN Remove rows containingkeywords. They should only bein the top, and will be here only wherecrossjoiner put them against the "wrong" keywordClose your eyes...Slight of hand...Now open your eyes...Pull the curtain roundWhich of my peoplegot on withyour people?cumulatively renumber the groupmarkersDiscard anyinitial"unloved" rowsat the beginningRemove unwantedcolumnsKeywordsMy people will talkto your people..!Keywords COMPONENTAllows join on regular expressionLeft Join + Actual Joinoutput on same portto bring together full setof rowsClose your eyes...Slight of hand...Discard anyinitial"unloved" rowsat the beginningRemove unwantedcolumnsNow open your eyes...Pull the curtain roundOne happy familyagainFill Grouping IDs downwardsthis does what the moving aggregation would havedone when filling in the group numbers.But this time we used Math Formulato provide the group numbering, so we need todo this part ourselvesCounter Generationplus rename(saves on a node!)String Manipulation Column Rename Row Splitter Counter Generation DuplicateRow Filter Concatenate Sorter Joiner GroupBy Transpose Ungroup Column Filter Rule Engine Moving Aggregation Row Filter Column Filter Extract Data Tablefrom Delimited Text Cross Joiner Extract Data Tablefrom Delimited Text Join Regexp_Like Joiner GroupBy Transpose Row Filter Column Filter Ungroup Column Filter Sorter Missing Value Math Formula

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: acampos
Created at: 2022-11-11
On NodePit since: 2022-11-13
Last update: 2024-11-21
Created with KNIME version: v4.6.3

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

Try NodePit Runner!