Hi all, I have used a Group By node to aggregate information from an Excel table. It has worked fine, but then I tried to do a Math Formula node, and the aggregate column (a “sum” column, with a “Double” data type") is unavailable for use. Any idea why is that? I simply wanted to multiply this column by -1, to turn the positives into negatives. Any help appreciated.
Welcome to the forum @kwikpils.
This is indeed a strange outcome.
Can you post the data and the workflow so that we can look inside?
Hi @kwikpils welcome to KNIME Forum
A strange situation indeed. Did you already replace the Math formula node with a fresh one from the node repository?
Gr. Hans
Thanks for the warm welcome.
Please find below, via link, a sample of the .XLSB spreadsheet - only 100 rows, the original has more than 500.000 rows – and the workflow. The column I’m trying to work with is called “Custo Desconto (R$)”. I removed the Group By node, and the column is still unavailable for the math formula, so the issue is not within the aggregation process.
Thanks! I did try that, and the column is still not appearing.
Hi @kwikpils , and welcome to the Knime Community.
I checked your link, but I ended up not getting the file - have to agree to terms and services which I don’t have the patience to read and I don’t know that site.
I think there might be an issue with your data, though Knime should still see the column and only complain about the data after processing it - the Math formula should only look for numeric columns when being configured.
I tried the following without any problem. I used this sample data:
The Math Formula can see the 2 numeric columns, including the Double one:
I also did a test with a GroupBy:
which generated this:
And the Math Formula saw the new column:
Can you upload your file directly in the forum?
EDIT: Ahh! Found the issue! It’s a column naming issue. It cannot handle the “$” in the name.
Here’s my new test table:
The Math Formula cannot see the column with name “Custo Desconto (R$)”:
You can rename the column before doing the Math Formula and restore the name after, that works.
Rename:
The Math Formula now sees the column:
Restore the name back:
Result:
FYI: Just a comment about what you are trying to do. Multiplying by -1 will indeed convert positives to negatives, but it will also convert negatives to positives. If you want to convert all positives to negatives and leave the negatives as negatives, then you should do abs($your_column) * -1
The abs() function converts the value to its absolute value (meaning it converts everything to positive), and by converting everything to positive and then multiplying them with -1 will result all of them to be negative.
Thanks for the reply! It is a naming issue indeed. Weird, but KNIME does not handle well columns with an ‘$’ sign within their title. I renamed it and it worked.
Regarding the multiplication by -1, what you said makes sense. Though this column specifically only contains positive values, so it shouldn’t matter in the end.
Thanks again for finding the solution!
No problem @kwikpils . It’s not so much that Knime does not handle well columns with a “$” sign, but rather that nodes that reference columns (or variables) reference them enclosed in “$” sign are the ones that have a problem. As you know, these are referenced as “$column_name$”, so if your column name has a “$”, that would not work.
But for nodes that either point to them directly, for example Column Rename node or nodes that retrieve their value via a function, for example Column Expressions where it would retrieve the column value via column(“column_name”), these usually would not have an issue.
So, alternatively, if you don’t want to rename, you can actually use the Column Expressions instead of Rename → Math Formula → Rename. You can do your multiply by -1 via the Column Expressions using the original name.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
