Replace non-null value with column name

I am looking for a way to replace any non “?” value in a table with the column name. Can anyone suggest a good way to do this? I am looking at creating something like this (so there might be an even quicker way of doing it). My current thought is to replace all 1 with the column name and then collect together:

ID Condition A Condition B Condition C Combination
AAA1 1 1 Condition ACondition B
BBB2 1 Condition B
BBB3 1 1 Condition BCondition C

I’m falling at the first hurdle though. Thanks!

Hello @JWebb
Your solution should be something very similar to this one:

You can avoid the cell splitters and locate a ‘Rule Engine’ node in workflow’s ‘Node 4’ position. The code can be something like:

Append Column: ‘Combination’

NOT MISSING $ColumnValues$ => $ColumnNames$

Let me know if further guidance is needed.
BR

2 Likes

The Rule Engine doesn’t recognise $ColumnValues$

However, I managed to get around it by running through an R snippet node and using this I stole from StackExchange somewhere:

df ← knime.in
df ← lapply(df, function(x) levels(x)[ x])
w ← which(df == “true”, arr.ind = TRUE)
df[w] ← names(df)[w[,“col”]]
knime.out ← df

1 Like

Hello @JWebb
Yeah, R is great.

The Rule Engine coding differs in syntax based on data type. A null/missed is not an empty cell either.

BR

hi JWebb,

I’ll always prefer using the native nodes over custom R-snippets for transparency and readability.

I believe a unpivot and groupby will do the trick.

cheers

/Martin

2 Likes

This is going to look like a shameless plug for my Rule Engine (Multi-Column) component (ok it is :wink: ), but in this situation it can assist because it extends the syntax of the standard Rule Engine by enabling the return of the “current column name”.

Returning the current column name wherever the value of a column is not missing is similar in principle to the solution from @gonhaddock

NOT MISSING $CURRENTCOLUMN$ => "<CURRENTCOLUMNNAME>"

Here is a demo workflow

and the component itself is here:

There’s a fair bit of complexity contained within this component, involving a recursive loop and a number of other embedded components so it isn’t going to be the speediest kid on the block but it will achieve the result.

My main reason for posting is that I think it makes a good additional use case for the kind of features that could be useful if ever an actual Multi Column Rule Engine node is developed.

3 Likes

I could certainly give that a go. What package is it in?

It’s a component, rather than a node so if you open the link I posted above you can drag it onto a workflow from the community hub. It was created as a proof of concept for how such a node might work, but it does function.

Unfortunately, the dragging function no longer works for me. I think it’s a setting somewhere. It’s quite frustrating, actually! Not sure I have the permissions to change setting either, alas.

@JWebb, You aren’t by any chance running KNIME with elevated (admin) permissions are you?

I have no idea! I just click it off the start menu. No special steps.

There’s a link on this post that shows how to determine if a process is running with elevated permissions.

And the post that follows it gives an example of KNIME running elevated

It might not be this at all but worth ruling it out for the drag and drop issue.

I have “Not allowed” under “UAC Virtualisation” so I assume it is running elevated. I also assume that I probably don’t have the requisite permissions to change this!

I think it doesn’t necessarily mean that. I think that if it is running as elevated then UAT Virtualisation will show “not allowed”, but it can show as “not allowed” for other reasons.

More important is the “elevated” column, if that says “yes”. My KNIME.exe shows “Not allowed” under UAC Virt, but is showing as “no” under elevated.

If you are able, and you aren’t infringing any local corporate IT policies, I would suggest manually downloading the zip archive for KNIME 5.3.1,
(e.g.
https://download.knime.org/analytics-platform/win/knime_5.3.1.win32.win32.x86_64.zip )

… extract that to its own folder, then run the KNIME.exe from there, and see if you can make that copy of KNIME work, and get everything installed that you need.