Injecting/Merging values from one table into a template table

Hello fellow KNIMErs,

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.

Thanks,
Gaetano

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):

vda-merger.knwf (18.2 KB)

I guess I’m missing something as it doesn’t keep the order of the template. Can I ask you to take a look?
Thanks.

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)

Hi there @gentile ,

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.

Br,
Ivan

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.

Is there a way to do that without scripting?

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.