Cannot replace Missing Values in Pivot (KNIME 5.2.0)
Hi all,
Can you help me with the following:
I have a table which is the result of a pivot populated by integer values and ‘missing’ values indicated by a red “?”. I want to replace the missing values with 0 (zero) however my efforts only replace the existing data, not the missing values.
The number of columns and column names changes dynamically depending on previous input into the pivot. The column names of interest are always prefixed by “I_” to facilitate filter selection.
I have tried Column Expressions, Missing value, Rule Engine, Math formula (MultiColumn), Group Start/End nodes with various coding ( eg
MathFormulaNode: if($$currentcolumn$$ == “?”, 0, $$currentcolumn$$)) to no avail. The Missing value node accomplishes the task of replacement perfectly but I don’t know of a way to allow for dynamically changing columns. The Math formula (MultiColumn) node does handles the changing columns as I can filter on the prefix (I_*) but the code I create does not replace the missing values.
I have searched related topics and implemented ChtGPT and BARD solutions but what i expect to work does not. Attached are screen shots and workflow.
Any advice would be greatly appreciated!
Workflow and Node configurations.
Output from MathMulti node.
Output from Missing Value node - Required output (number of columns and names change depending on pivot)
Workflow.