I have a table with a “Service” column. I then have an unknown number of columns called “Service_0”, “Service_0_amt”, “Service_1”, “Service_1_amt”, etc.
I need to create a new column called “direct_amt” that will pick up the “Service_0_amt” if the “Service” value matches to the “Service_0” value. The “Service” value will only ever match up to one of the new “Service_0” columns.
Hi @tonyjmeade , in your last paragraph, did you really mean “Service_0” or did you mean “Service_n” (i.e. direct_amt should pick up Service_n_amt, where Service matches Service_n )?
I’ll make an assumption that this is what you wanted. While I (and no doubt others) ponder on a way to do this using standard nodes, here is a method using one of my components, “Rule Engine (Multi Column)”
As well as working with multiple columns, the “multi column” component provides additional features over and above the core Rule Engine, as it allows you to dynamically reference column names:
With luck, hopefully the KNIME team will consider a future Multi Column Rule Engine node with similar functionality, as I believe this would be well received by the community…
Anyway… what this does is create a new column with a suffix #amount# for each Service_n column, with the amount value if the value matches, and missing otherwise
The values in these #amount# columns are then aggregated to retain only the non-missing value in each case, and renames it as direct_amt. Finally the Auto Type Cast is used to return this column as the required numeric type, as the type could be unknown (as in my demo data)
If you are interested, the Rule Engine (Multi Column) component is available here:
@takbb that has done exactly what I needed (and you were right in your assumption!).
Thanks for your help!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
