Icon

KNIME_​unique_​concatenate_​with_​count

Data Preparation.Add a row numberAdd a row number lag (-1)Get Row Count into flow variable "Number Rows" Split the data cell into a list,divided at the comma, thenungroup the list into separaterows Split the derived row valueinto key and value using regexWe then have row number plus Key Valuepairs to work with Join a copy of the table to itselfjoining on Previous Row - Row,and "key" (e.g. fruit name), so wecompare each row with the next Fill in any blanks, where there was no matchingkey between one row and the next Calculate new difference in valuebetween each key Construct a new "row key" for thedifferences in the form rowN-rowM Regroup into concatenated listgrouped by the new "row key" toachieve required output format Sample Data SetTurn comma separated stringsinto a List to enable Ungrouping into multiple rowsCreate a -1 column based on Row NumberCreate a counterto use as row numberDivide each rowinto multiple rowsRename toRow NumberDeterminecolumn Keyand ValueRename to Key and ValueCalc Difference for ValueMake Value col NumericRemove missing "previous" rowsfor row 1Derive new concatenatedkey(value) stringremove superflouscolumnsGroup by rowN-rowM keyconcatenate stringDerive new"rowN-rowM" keyGet Original Row Countfor use laterRename columns "Earlier" and "Later"to make it easierto work with laterRemove entries for final row compared with"next" (non existant) rowThis is where we usethe row count "Number of Rows"collected at the startFill Missing Values with 0Populate othermissing columnsfrom correspondingrowNode 64 Table Creator Cell Splitter Lag Column Counter Generation Ungroup Column Rename Regex Split Column Rename Math Formula String To Number Row Filter String Manipulation Column Filter GroupBy String Manipulation Extract TableDimension Column Rename Rule-basedRow Filter Missing Value Column Expressions Joiner Data Preparation.Add a row numberAdd a row number lag (-1)Get Row Count into flow variable "Number Rows" Split the data cell into a list,divided at the comma, thenungroup the list into separaterows Split the derived row valueinto key and value using regexWe then have row number plus Key Valuepairs to work with Join a copy of the table to itselfjoining on Previous Row - Row,and "key" (e.g. fruit name), so wecompare each row with the next Fill in any blanks, where there was no matchingkey between one row and the next Calculate new difference in valuebetween each key Construct a new "row key" for thedifferences in the form rowN-rowM Regroup into concatenated listgrouped by the new "row key" toachieve required output format Sample Data SetTurn comma separated stringsinto a List to enable Ungrouping into multiple rowsCreate a -1 column based on Row NumberCreate a counterto use as row numberDivide each rowinto multiple rowsRename toRow NumberDeterminecolumn Keyand ValueRename to Key and ValueCalc Difference for ValueMake Value col NumericRemove missing "previous" rowsfor row 1Derive new concatenatedkey(value) stringremove superflouscolumnsGroup by rowN-rowM keyconcatenate stringDerive new"rowN-rowM" keyGet Original Row Countfor use laterRename columns "Earlier" and "Later"to make it easierto work with laterRemove entries for final row compared with"next" (non existant) rowThis is where we usethe row count "Number of Rows"collected at the startFill Missing Values with 0Populate othermissing columnsfrom correspondingrowNode 64Table Creator Cell Splitter Lag Column Counter Generation Ungroup Column Rename Regex Split Column Rename Math Formula String To Number Row Filter String Manipulation Column Filter GroupBy String Manipulation Extract TableDimension Column Rename Rule-basedRow Filter Missing Value Column Expressions Joiner

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: @takbb Brian Bates
Created at: 2021-05-04
On NodePit since: 2022-02-06
Last update: 2024-04-27
Created with KNIME version: v4.3.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!