Using List Files Node to create a Variable for Updating a Column in a Join

I am working with a List of Files with Filenames that reflect Column Names in another table.

I would like to

  • loop through each File
    -Open File to perform a Join
    -Set the entire column to be False for all rows
  • Matching Column of Filename is set to “True” for the column that represents the Filename.
    -Loop until complete

Illustration on above is as follows:

  • loop through each File

-Set all rows to False in Match Column and then Join to set them to be True.

After getting the Filename from file, locate column that matches or use Filename as a Variable for the ColumnName to execute the join operation.
Using something like “column(variable(“Filename”))”"


Use the File to get the matching rows to use in a join

-Set the entire column to be False for all rows

  • Matching Column of Filename is set to “True” with a Join based on pro_Part_Code for the column that represents the Filename and all other rows should remain False of course.

Thanks for any help

For what it offers, it appears this may have the solution…

located at

Hi @DavisRogers -

That’s a great workflow that @TardisPilot put together. If you can take that as a starting point, maybe you could come up with your own workflow using a small subset of your data? Then we could help you refine and troubleshoot that for your particular use case.

Good luck!

Thanks @ScottF,

I got a pulse, but I am needing a little more dynamic result. In the Rule Engine Node I have the following.

$$ROWID$$ LIKE “?” => “yes”
$File name$ = 0=> “False”
$File name$ > 0=> “TRUE”
TRUE => “ok”

This will find all of the rows that have “?” and update the Column that is specified however,
I am looping through the files and I need to update the column that is a variable within the loop. The variable name is $File name$. But this is just the column name that I want to update with True. I was thinking of something like column($File Name$) > 0 = “TRUE” but that did not work and it also still wants me to select a column to apply this to which in my case the column will always change as it goes through the loop.

Maybe the Column Expression Node would be a better approach. I don’t know if that is a different application or not for updating a Table Column? Any Thoughts. Thx

Thanks @ScottF and @TardisPilot,

If I could write a formula to tweak what I currently have working it would look something like the following…

If $$ROWID$$ LIKE “ ? ” => “yes” Then column( variable($File name$)) = “TRUE”

Is that wishfull thinking in another node?

Hi @DavisRogers -

There is a bit of a trick to identifying missing values in the Rule Engine node, but it’s possible. Try

MISSING $$ROWID$$ => "yes"

You’re right that the Column Expressions node will probably offer a more dynamic solution, though. It can be challenging to put together complex conditionals using just the Rule Engine, since you may need a series of Rule Engines nodes to get it done.

I would encourage you to upload your workflow with a dummy dataset, if you can - you’re much more likely to get people tinkering with it that way, as opposed to just screenshots.

Thx @ScottF,

I will attempt to go to the Column Expressions node with some Flow Variables. I appreciate the insight, very helpful.
I will update a sample or solution in a bit.


Hi @ScottF and @TardisPilot,

Here is my attempt. Forgive my scatter brain on the idea, I am just trying soo many different things which make me feel that it is easier than what I am attempting.

KNIME_project1.knwf (44.4 KB)

Hi @ScottF and @TardisPilot,

This might be close - “Loop in KNIME on a list of unique values”

Merges two columns into one by choosing the cell that is non-missing.

Loop in loop with “table row to variable loop start”: problems accessing flow variables

It all looks promising.

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