I have a table with two columns. They consist of a name and a value column (they are the result of an ungroup node that I use to ungroup two collection columns to multiple rows).
ColumnNames
ColumnValues
Name1
“Value1”
Name2
“Value2”
Name3
“Value3”
Name5
“Value5”
Name6
“Value6”
Name3
“Value3”
Name5
“Value5”
Name6
“Value6”
I have a template that provides all possible names and the explicit order they must follow (yes, they occur multiple times).
ColumnNames
ColumnValues
Name1
" "
Name2
" "
Name3
" "
Name4
" "
Name5
" "
Name6
" "
Name1
" "
Name2
" "
Name3
" "
Name4
" "
Name5
" "
Name6
" "
Is there a simple way to use the template and “inject” (or merge) the values from the table above and keep the template structure (like below)?
ColumnNames
ColumnValues
Name1
“Value1”
Name2
“Value2”
Name3
“Value3”
Name4
" "
Name5
“Value5”
Name6
“Value6”
Name1
" "
Name2
" "
Name3
“Value3”
Name4
" "
Name5
“Value5”
Name6
“Value6”
I have looked through the forum, but haven’t seen any post that could help with this problem.
First, using Joiner make left join of template table with values table by Column name field.
Second, use rule engine to return values if they are filled like
NOT MISSING ColumnValues => ColumnValues
TRUE => Templ_ColumnValues
Thank you izaychik63. I had played around with the joiner yesterday, but gave it up as it did not show me the right order and was grouping the column names in an unexpected way.
Here is the workflow you suggested (containing the data and template I am trying to process):
I see you used Full Outer not Left join. Also, it is not clear for me what do you mean by order - row order or column order? For rows you can use Order by node for column Column Resorter. To filter necessary columns use Column Filter.
See attached WF vda-merger.knwf.knwf (22.3 KB)
have checked data in workflow you attached and seems data from first post does not represent it very well. You have got nested repeating in both template and data and number of “nesting” does not match. Also template has 134 rows while data has 222 rows. To me it is not exactly clear what your input structure is or can be so giving suggestions is a bit hard. Anyways if I were you I would try some scripting language for which KNIME offers support (Java, Python, R, JavaScript). At least for some part of your task.
Apologies for creating confusion and thanks again for taking the time. Really appreciated.
@izaychik63: I had tried left outer join and also other joins (and accidentally left it on full outer join when exporting the workflow). By order I mean the row order. Your output looks pretty good, but I think we’re not there yet: too many repetitions of the same names. Plus, the order of the values is not maintained as it is in the data table.
@ipazin: I realize that the idea with using a template is not very clever, as my data tables can be very different in row count and nesting behaviour. And a single template will not be able to match all of the scenarios.
Basically, what my goal is: to fill the data table with new rows where there are gaps.
Meaning: if there is a row with name 713.715.08 and the next row is 713.715.10, I want to fill in 713.715.09.