Version 1.1 2021-02-22
There are occasions (for example, after merging several tables or after a loop workflow process) where one has various columns containing exclusive values in each column. As such, it is often required to merge all these columns into a single column which contains all the values. Viz:
Before merging (3 columns with unique, exclusive values):
ColA,ColB,ColC
1,,
2,,
,3,
,,4
,,5
After merging (1 column with all values):
ColA
1
2
3
4
5
This workflow proposes two strategies for completing this task.
Strategy 1: Using Column Aggregator and Ungroup nodes
This strategy uses the fewest nodes. The Column Aggregator aggregates all the values in each row into a list, thus creating a singular collection column of lists as the output. The list is then deconstructed using the Ungroup node by extracting the unique value from the list. This process leads to a slight modification of the RowIDs.
Strategy 2: Using Column Merger and Recursive Loop nodes.
In this strategy, the column merger is applied to these columns via a recursive loop. This approach is quite similar to a previous workflow published (Merging Columns from Full Outer Joiner node - https://kni.me/w/MA7IE1-N-s983D11). Within the recursive loop, the first column is designated as the main (primary) column of which the values are merged into, while other columns are designated as the secondary columns in the Column Merger node.
The number of iterations for the recursive loop can be determined by calculating the number of columns and subtracting one from it.
This strategy preserves the RowIDs of the input table.
It is hoped that this workflow can be useful for those who require a dynamic approach to merging all exclusive values in various columns into a single column. Any questions and/or feedback will be much appreciated!
Workflow
Merging Exclusive Values in Various Columns into a Single Column
Used extensions & nodes
Created with KNIME Analytics Platform version 4.3.1
- Go to item
- Go to item
- Go to item
- Go to item
- Go to item
- Go to item
Legal
By using or downloading the workflow, you agree to our terms and conditions.