How can I add a row to a table keeping the structure the same?

[CopperLGrant]

I have a table that I would like to insert a row with specific values in. I searched for an “Insert Manual Row” node and similar but couldn’t find anything. Only Insert Empty Row.

I see that there is a Create Manual Table node, which doesn’t suit my needs, as I need to ensure the rows to be created match the structure of the destination table and would prefer to do this programmatically - hardcoding is something I use KNIME to avoid.

I tried Extract Table Spec, which looked promising, but I couldn’t find a node that accepted the spec table as an input, which would also then let me stuff the row with the manual data.

Any thoughts or wisdom? Thanks in advance!

[elsamuel]

Welcome to the forum, @CopperLGrant.

The Concatenate node will allow you to add the row.

You can extract column headers into a single column using the Column Name Extractor node then use those some rules in a Rule Engine or Column Expressions node to populate the values in another column.
A quick transpose and concatenate should get you your desired result.

[takbb]

Hi @CopperLGrant , if you want to be able to type the values into a table, there isn’t a solution that will provide a “Table Creator” with the column names populated, however there is a potentially workable solution which still uses Table Creator…

You have mentioned using Extract Table Spec, but instead you use Extract Column Header (KNIME 4.x) or Column Name Extractor (KNIME 5.1), you can then get the column names on a single row, rather than as a column.

In KNIME 4.x, or if using Classic UI in 5.1, you can inspect the output of the upper port, and copy and paste the column names into clipboard and then paste these into the first row of a Table Creator.

To copy the row data if using Modern UI in KNIME 5.1, you will probably have to add an Interactive Table (legacy) node (Interactive Table (local) in 4.x, after the Column Name Extractor and then right click on it display the view in order to copy the row data.

Once you have your column names, paste them into the first row of a Table Creator. Add your required data on rows 2 onwards, so you can use the column names in row 1 as a guide to the required data.

Add a Row to Colum Header node (4.x) or Row to Column Names node (5.1) and have it use row 1 as the column titles.

Send this to a Table Validator (reference) which uses the primary data source to validate the table, and configure that to convert data types where possible, and fill in missing columns (if appropriate). You can then concatenate the output back into the main table.

Add manually entered row.knwf (16.7 KB)

Technically, I would say that pasting column names into Table Creator still counts as “hard coding” of sorts, which I know you want to avoid, but it is difficult to see how you could have potentially dynamically changing columns in a workflow whilst still including manually specified data values without some element of hard-coding. However it feels “less” hard-coded than if you manually edited all the Table Creator column names and data types.

[iCFO]

Couldn’t you just use the counter generation node to get the row numbers of each table, then do a join on the row count to separate out the missing ones into another stream, then use concatenate to drop the missing ones with values at the bottom of the target table?

[takbb]

Hi @iCFO , Not quite sure I follow.

Because the (Manual) Table Creator is not going to be configured at all, it is going to be creating String data for everything, and then the Row to Column Header will attempt to determine the domain of the data that’s been entered.

If any columns contain missing data, it cannot determine a datatype and becomes an “unknown” datatype. That can then cause problems as Concatenate then makes the corresponding column in the main table an Unknown type too. By using the Table Validator, it attempts to coerce all columns in the manual table into the data types of the original table, so columns containing missing data are correctly resolved. Then it works ok on the Concatenate node.

It’s always possible I’ve missed something and/or over-engineered it

[iCFO]

I probably just misunderstood the original question. I was thinking it just two matching table structures, and in the event one was shorter than the other he would fill the remaining difference of rows with the bottom ones from the longer table.

[CopperLGrant]

Thank you everyone for your replies, it is very comfortable to know there is such an active support community.

I think takbb’s answer suits my use case as “best workaround”, and I will implement that.

I would like to suggest an “Insert Manual Row” node - is there a place I can raise this feature request?

Grant

[takbb]

Hi @CopperLGrant ,

There is a feedback/ideas section of this forum where you could post. You could out a link back to this question for context. It has a vote button on it so people can indicate that they like the idea. The votes are only there as an indicator and no idea tends to get more than a few votes so don’t be disappointed if it gets none!

Also regardless of the votes there is no guarantee that any idea will get implemented but it’s all useful feedback and I believe the guys at KNIME read through these in their internal meetings.

Feedback & Ideas

I can see the value of your idea, but my only question is in practice how it might work as you effectively have a dynamic table which could change (in theory) between executions, but it would maybe have to accept user entry similar to Table Creator (only hopefully better, ) so I wonder how it would match the values entered with the column names over time.

ie if you reran but had modified the workflow upstream so now a column has disappeared, or columns are in a different order… how would it keep track? Does it error or does it just ignore “now-unknown” columns?

Anyway as I said I think it sounds useful and would be interested in ideas of the mechanics of it… these are all things that could be discussed to assist with such a node being developed if it’s feasible.

[Daniel_Weikert]

@CopperLGrant
well a manual row kind of exists if we combine the Add emtpy rows with the Table editor node. We should be able to the fill the values of this row
br

[takbb]

thanks @Daniel_Weikert , how have I managed to miss that one all this time?

[iCFO]

The Table Editor (JavaScript) node should always come with a portability warning… It cannot be simply copied and pasted, as it will lose all of the table edit info. Not I have found a way to copy and paste the node settings from the underlying folder in the workflow. It also can only be copied and pasted one cell at a time, so it is much less “hard coded” than it appears.

I have components that create / store / backup / recover these settings in log files, but it is certainly not dynamic and self adapting to changes in the Table Editor structure. It currently would require a use specific rebuild.

[CopperLGrant]

Thanks all for your thoughts. Certainly a lot to consider. I think my intent is that I only have to define the table structure once in any workflow definition - obviously nodes change the structure programmatically but that’s a sequence of known changes. Table Creator requires me to define the structure twice and means I can’t fluidly add a row manually without checking two sets of column definitions to check they’re identical.

An “add manual row” functionality would take the table structure into account when adding the row and the user would (likely in a grid control for practicality) simply provide values to be written into each column provided by the “add manual node” row. The user is unable to supply values to columns that don’t exist because the table structure is taken from the existing table.

An equivalent OOP example in pseudocode would be something like:

myListObject.create();
myListObject.addColumns(“name”,“age”,“favourite colour”);
myListObject.addManualRow(“name”:“Bob”;“age”:“23”);

this .addManualRow method doesn’t require me to know or maintain the “favourite colour” column and would error-handle me trying to update the “address” column. This would be even easier in a KNIME node as a visual grid with prepopulated column names already hints the user about acceptable inputs for the manual row.

Thanks for listening!

Grant

[iCFO]

It is much better to think along the lines of components when you get to specific needs like this. It wouldn’t be hard to build an interactive view component that incorporates both the Table Editor (Javascript) node and the Insert Empty Row node with the refresh button. You would concatenate in the Empty Row Insert (widget controlled by flow variable) to the bottom of the table in front of the Table Editor, then if you adjusted the widget for the number of inserted rows and hit the refresh button they would be added to the bottom of the table.

I can’t really comment on dropdown selection for certain columns as that would be task specific and I have not context on your project, but it is easy to incorporate all of the options existing in the column domain for selected columns. Since use cases differ so much, it might require some customization to really dial it your row entry method by column for the specific task. However, all of the columns could be set to dynamically exist in the Table Editor. I believe that changes to the table structure would clear those prior manual row entries, so it would have to be addressed on every update if the table structure was highly variable.