Hi
I am very new to Knime but I have found this tool full of potential and certainly enjoying using it.
I am trying to do a simple calculation involving one column dividing by another column, and I would like to do it for all 100 columns in the sheet. I can create the calculation with column expression node but I thought it was a bit inefficient since I had to set up the formula for every single column. I was wondering if there a more efficient way to do it? my gut feeling is that it may involve a loop. Thank you so much
If you are doing only calculation and are dealing with numbers, you can use the Math Formula node, which also has a version where you can apply the same calculation to multiple columns, and that node is called Math Formula (Multi Column)
Please take a look at them and let us know if you have any questions.
Thanks for the suggestion, but I am not to sure if that works for me.
I have attached my sheet here. What I have are two sets of calculation for each state. MA and MAT. I was trying to have MA divided by MAT for each state. I used column expression but found it a bit time consuming. Any suggestion would be welcomed. Thanks so much. example.xlsx (201.1 KB)
There are probably a few ways to do this, but I think that the easiest way would be to restructure the data.
I find that new users to KNIME tend to think in Spreadsheet Mode (I’m not saying that you’re doing this), and it takes a while to realize that a KNIME table is not a spreadsheet. In KNIME it’s often much more efficient to work in rows compared to columns, and I think this is a prime example.
I think that this task would be much simpler if you had a table with 4 columns: Geography, State, MAT value, and MA value. Having a table with 8600 rows and 4 columns is easier to work with than a table with 167 rows and 105 columns. The final calculation result would be a single additional column instead of 100+, which makes using the resulting value a lot more straightforward.
It seems to me that the data you provided was pivoted at some point. Do you have the unpivoted data?
If not, you can come up with a workflow that does this, and it’ll take far less effort than trying to set up all the calculations in a Column Expressions node or multiple Math Formula nodes or to try to create a loop. You can always pivot the data again later if you really need that structure.
Hi @davidlan , alright, since you are using pairs of columns for your calculation, it would be kind of difficult to do this via the Math Formula (Multi Column) indeed.
I put something together that does it another way, and it looks like this:
Finally I have a larger collection about various sources and examples how to learn KNIME depending on how you like to learn (books, videos, examples, live teaching):
