Joiner Not Working in Loop.

[saiakula15]

Hi Team,

I am new to KNIME and this is my First post.
I have two questions for creating a workflow.

1st Question In Source file I have around 90+ Column as Header in one sheet and i have a list of 20 Column Name(in Rows) in another sheet.
How can I eliminate other unnecessary Columns using Joiner as the loop instead of Processing all the 90+ Columns.
this will help me process the Loop for only necessary fields and saves a lot of time.

2nd Question I am trying to use Loop with Column Rename Regex and Joiner.
Few field have Special Characters in the Header like (#1) and Column Rename Regex is throwing an warning and the field name is not getting renamed and an error is occurring at Joiner as the name is not Matching.
Below is a screenshot for your reference.

Also Please find the attached workflow as well.
Could you please help me with the above request.

KNIME_Test_Project_V1.knwf (85.7 KB)

Thank you for your help in Advance.

[ipazin]

Hello @saiakula15,

and welcome to kNIME Community!

Not sure you need loop if all you want is to eliminate columns from source file based on a header list from another file. All you need is Reference Column Filter node.

If you need to make Joiner node work in a loop take a look at this topic:

Joiner node in Loop

Hi all! I’m trying to use Joiner node in a loop. I start loop with a Table Row To Variable Loop Start which takes table name as input. That table is read from one database and then compared to the same table in another database. In order to do this I’m using Joiner node for Right excluding join. In order for this to work properly I would have to make Joiner node to take all columns from both tables (they have same names) as joining columns. Ideas? Br, Ivan

Br,
Ivan

[saiakula15]

Hi @ipazin,

Thank you for your reply, i want to make the workflow dynamically as this workflow could be used by others in future, so i dont want other users to make any changes in the workflow. all they need to do is just select the required files and execute the workflow and they should be able to get the O/P.
This is the reason i was trying to use Joiner in Loop so it can select the required fields and execute it.
Also, i have tried with Column Filter widget but it did not worked

Thank you.

[takbb]

Hi @saiakula15 , unfortunately we cannot execute your workflow, or see what it is doing because the Excel files that are the source are local to your environment and not included in the workflow. If you execute the workflow and then upload it again but make sure you have unticked “reset workflow” when exporting, we will be able to run it with the data.

As @ipazin said, the Reference Column Filter node should be able to filter the columns without a loop. I’m not sure why using the Reference Column Filter node should mean other users will make changes to the workflow, but maybe I’m misunderstanding.

What problem did the Reference Column Filter give you when you tried it? That may be an easier problem to solve than the one you are posting

There are two ways you could use the Reference Column Filter.

It is designed to receive a table that contains the “reference columns” that are to be kept, and filter the other table on that basis. So if you had two columns you wished to retain “EE ID” and “EE Name”, you could set it up as follows:

The Table Creator would look like this:

Now I can see that if your list of columns to keep were to be dynamic, then maybe this wouldn’t be ideal as the Table Creator would need columns added/removed. So, this could be adapted to a similar way to how you have it.

If the Table Creator were configured as follows:

You could still use it with the Reference Column Filter mode, but you would just need to transform it:

5.1

If using KNIME 5.1, a Table Transposer followed by a “Row to Column Names” would adapt the table to the input required by the Reference Column Filter

4.x
If using KNIME 4.x, a “Transpose” node followed by a “Row to Column Header” would do the same job

Re your second question, without being able to run your workflow, we cannot tell what the error your Column Rename (regex) is throwing, nor what column names are causing the problems. If you can give an example of the column name and the regex being used, along with the warning message, or as mentioned above post a runnable version of the workflow, somebody can probably assist.

[saiakula15]

Hi @takbb,

Thank you so much for your help , i was able to work with “Reference Column Filter” and was able to select the required fields.
As per the second question please review the new Workflow and Sample Files required for the workflow.

KNIME_Test_Project_V1.knwf (258.6 KB)
Sample file.xlsx (12.9 KB)
Sample Mapping Document.xlsx (54.6 KB)

Note: The Sample File Should be selected in Excel Reader(Read Source)

Sample Mapping Document should be selected in the Mapping Document MetaNode.

Thank you.

[takbb]

Hi @saiakula15 , thank you for uploading the worksheet and sample spreadsheets.

Within the loop you are simply wanting to only rename the “current column” to “Data” and back again, so rather than using Column Rename (Regex), it would be better to use the normal Column Rename nodes.

Configure the first one as follows:

It will probably appear differently when you configure it, and may show a different column name.
Simply choose any available column in the list, and set it to Change to “Data”.

Then set the flow variable for old_column_name to “currentColumnName”

Then in the second node, choose the Data column

and configure the flow variables to set new_column_name to currentColumnName

I hope that helps get you a little further.

[saiakula15]

Hi @takbb,

Thank you for your help, i was able to solve the issue.

Thank you,
Sai Akula.

[takbb]

Hi @saiakula15 , you are welcome and I’m pleased you managed to resolve it.

Thank you for confirming the solution.