Do we have a node in Knime, which works same as Mutli Row Formula in Alteryx ?
Hey @Rishabh,
not directly, but you could do it with a combination of the Lag Column node and the Math Formula node.
This is nicely described in our book From Alteryx to KNIME (Page 21). The book is free of charge and will give you useful information on how to do things in KNIME compared to Alteryx.
Cheers,
Julian
5 Likes
2 Likes
Not widely known yet, but they added it with the latest version into the Column Expressions Node!
Edit- Looks like you beat me to it @Alejandroc!
2 Likes
Advanced settings …
2 Likes
We knew what you meant. 
2 Likes
I worked at Alteryx for over three years and have just retired. Can’t afford the 5K a year to keep a license so I’ve been looking at KNIME as an alternative. But this lack of multirow functionality has been a real pain. I’ve been poring over every site I can find but still haven’t seen a way to do multi-row calculations that actually use the computed result for the previous row in the next row (other than coding). The multi-row extensions to the Column Expression node don’t quite get there. A simple example is a factorial calculation: Let’s say I have an index column with numbers from 1-10 and want another column (call it F) to have the factorial of the number. I’ll create a dummy column called “F” and let’s assume it contains all 0’s. If the Column Expressions node operated on computed values I would expect the expression column (“F”, -1) * column(“index”) to return the factorial for each row. But it actually returns all 0’s. Apparently, the node is just creating a bunch of temporary lag columns and using those for the calculations.
Other than that node, the only thing I can think of is if there is some way of doing loops where the result table of each loop is passed into the next iteration.
I’m sure there is a way to do these kinds of operations in KNIME, but so far I haven’t been able to find it. I freely admit my experience with these kinds of tools is primarily Alteryx so I may just be looking in the wrong place. So to put it simply, if I have a complex running calculation that could involve multiple rows and columns and needs to be propagated through a table, what are my options?
@Bytecrawler I see these options:
- recursive loops - where you can bring back information from the end
- you can have operations per whole column like COL_MAX etc in the math node
- you have a Moving Aggregator – KNIME Community Hub
I think you will have to lay out a plan and combine these functions/nodes. Maybe you can construct an example that you would want to solve that would fully represent your challenge.
I agree with you @Bytecrawler, the only one node solution in KNIME capable of the equivalent of the multi formula in Alteryx is the Java snippet. Details referred to in this thread…
2 Likes
Thanks very much for the quick reply, @mlauber71. I hadn’t looked at any of these. Recursive loops sound promising.
I’ve spent some time with all the options @mlauber71 mentioned. Things like Moving Aggregator and COL_MAX will help in specific cases. I’ve built some solutions with recursive loops and I think I can cover anything that Multi Row Formula can do using that approach. But the only way I could figure out do it is by setting a counter and running each loop over the entire table, using the iteration counter to identify which row to work on during that iteration. So I’m doing n-squared row accesses for an table with n rows. OK for small tables but I’ll need something better for large data sets. When that issue comes up with KNIME, I suspect it will be time to look at java snippets (thank you, @takbb!) although I was trying to keep this code free.
2 Likes
Hi @Bytecrawler , yes I try to keep it code free where i can, but there are some situations where for my sanity i think that a small amount of code is sometimes needed… , 
1 Like