Hi how to I code this in KNIME ?
Given the col we are trying create is C
if ( column(“A”) = column(“A”,-1)) then
X - column(“B”)
else
column(“C”,-1) - column(“B”)
I know there is a group loop + moving sum to achieve this… but not efficient.
I would like to achieve that in formula.
in Alteryx, I can do this very easily using multi row component … but in KNIME, I ran out of options …
Maybe a java script ? I am not good with Java so not sure how to do that .
Gordon
Welcome to KNIME Community Forum! This looks something like Excel…? 
Don’t quite understand what are you trying to accomplish. To avoid guessing can you provide small input data and expected output and logic if not clear from data? This is proven to be the best approach to get help/advice/solution .
gr, Hans
The Column Expression node is capable of handling this nowadays.
But as @HansS mentioned, please provide as much information as possible.
This can help out:
Yes indeed. this is very very easily achievable via excel … a 3 year old can achieve… but in KNIME I. can not refer to current column offset items
The problem I am trying to produce cumulative sum of a column given a condition …
table can be like
Year | Loss
2018 100
2018 200
2019 300
2019 400
resuling table would be
Year | loss
2018 100 ← 100 + nothing because it first
2018 300 ← 100 + 200
2019 300 ← 300 + nothing
2019 700 ← 300 + 400
there are several ways to do this within the workflow.
but I just want to solve this solution using 1 formula that can call value of itself offset 1… it is sooo hard to achieve this in KNIME given my expertise in KNIME
else I won’t register a community forum account and ask for help.
@ArjenEX here you are missing the point. there is that formula, I am not stupid . I have spent sufficient amount of time on google and other forms tried to find solution. this is not just a problem where I call offset of a speficif column, I am trying to call prior number of the column I am trying to create… Before you tell someone else to search the forum. please make sure you understand the underlying problem I am trying to solve .
Thanks
I would like to achieve this because all other methods will add runtime to my workflow… and this is so easily done in Alteryx using multi row formula. I do not understand why KNIME is not able to perform such an easy task.
I think you missed what @ArjenEX was trying to tell you… They have very recently added multi-row support to the Column Expression node that is somewhat comparable to the the Alteryx Multi-Row Formula tool, however most of us have not used it enough yet to just write JavaScript expressions directly on the forum with high confidence.
The other post was not an attempt at calling you “stupid” or any other negative attack. It was to give you guidance on the most efficient way to ask for help on the forum.
If you want someone to provide you a “workflow solution”, then the best bet is to take the time to put together some dummy data, do an export (uncheck the reset option to ensure your dummy data is included), and then upload it to the forum. As far as the users that I know, we are all working high stress day jobs and don’t have time to try and draft up our best guess at representative dummy data in order to work on possible solutions.
These are all just good forum guidelines for a quick solution. None of them are personal.
Hi @KNIMEuser23421 , Good luck…
In fact, we have tried. If two people mention that you didn’t provide enough information, maybe it’s actually the case? People that regularly visit the forum and go through all the topics see these text only - zero visual reference topics multiple times a day. Imagine how much time and effort is wasted on keeping asking for more information.
Just a tip for next time, try to include as much as possible even though it might be every obvious for you. For others it’s usually not. Think of current input, expected output, what you have tried so far, attaching your workflow etc. The information that you provide in your second post is exactly the type of context is required. People are so more inclined to help you out then.
Other than that I echo @iCFO
Make that three. And I am sure as more people read the thread, they will agree that there was not enough information that was provided at that point (first post). I had visited the thread and I did not understand what was being asked originally, and since @HansS and @ArjenEX had already asked for clarification, I did not jump in and was waiting for the clarification.
@KNIMEuser23421 I echo what @iCFO said, and I totally support and agree with what @ArjenEX has said. Indeed, people will be more inclined to help when the proper information is given (as I always say “help us help you”), but above all, attitude is the most important factor.
With this attitude, I wish you Good luck once more.
Ok, make that four! I was almost inclined to just walk on by but assuming you are staying…
In your first post you said you had a formula which you seem to think is so perfectly clear that even a 3 year old with Excel could work it out… so let’s review that statement:
First off, providing pseudocode with no explanation of what you are trying to do is generally a bad idea. There may be plenty of people who might be able to help but don’t understand your code.
So I read this as:
if the value in Column(“A”) equals the previous row’s value in Column(“A”), you are wanting to populate Column (“C”) with a value of X minus the value of Column (“B”)
Otherwise you want to populate Column (“C”) with the previous row’s value in Column(“C”) minus the current value of Column (“B”)?
What could be clearer? Except… erm… What is X?
So that’s the first point. As others have said, that most definitely wasn’t clear for everybody else even if it was clear for you.
Then, in your subsequent example
… erm… why are you now adding when before you were subtracting?
So many questions.
Now @ArjenEX actually pointed you in the right direction with mention of the Column Expressions node, but rather than give thanks and investigate further, you decided it was better to have a rant.
Did you try writing a formula in Column Expressions? (You will need to go to the advanced tab and enable “multi row access” with a window size of 1 I think. Seems to work, but I know you will know that already because of all the searching you’ve done)
if (column("Year")==column("Year", -1))
{
column("Loss")+column("Loss",-1)
}
else
{
column("Loss")
}
yes I agree, there are some people who might find that difficult.
Although maybe your three year old Excel guru would have coped admirably?
You will note that the formula is not like your pseudo code. I’ve based it on the data sample in your later post which is also nothing like your pseudo code.
Anyway, welcome to the forum. If you do decide to stay, I strongly suggest that you change your attitude. It is not every day that somebody manages to annoy some of the best and most helpful and friendly contributors on this forum. If you don’t stay, then… well… I’m with @bruno29a in wishing you good luck. btw How much does an Alteryx license cost these days? 
no further comments needed I guess but the community experts get support and a 
from my side.
just one small edit (please forgive me for that):
“I do not understand why KNIME is not able to perform such an easy task”. I also come from Alteryx and I have not read in one single line in this thread that KNIME cannot perform this task
br and have a great weekend guys.
The logic I have explained here was pretty clear in my previous message and I can achieve that solution by using some other logic. However, I was frustrated and disappointed at KNIME that it does not have function or feature readily available to let a column refer to itself.
Also, when people come to the forum, I hope you understand that there are some people do not just come here if they have questions. People like me, will spend sufficient amount of time search on google and try some other ways around to achieve the what we want rather than coming to the forum and post it and wait for someone the respond? to me coming to the forum is the most inefficient way of getting solution. Not a lot of world business problems have patience to wait for me to go on the forum and wait for someone to provide solutions.
What triggered me was that guy , without looking at problem carefully and throw me a solution which is wrong and which I have been trying to use to solve the problem. and throws me another comment saying I should do more research. This is not how you treat questions from other people. This is disrespect. Before you look at questions other people have, would you please make sure you understand what the fundamental issue is and please respect the fact that some people have spend sufficient time on trying to solve the problem on their own and they come to this form is because they ran out of places to get answer?
The issue here is simple, doesn’t matter if I add or subtract, I just want to see if KNIME, a column is able to do cumulative sum. In Alteryx, there is multi-row where you can refer to previous value of a column you are creating.
And again, this is more a simple matter of using column expressions. In the column expression you need to refer to column you are currently creating.
Btw, I stopped using that Old piece of Junk (Excel) so many years ago. who uses Excel do to data analyst work these days ?
Also, regarding Alteryx license, let me tell you that 5k USD, is really insignificant compare to the values we are generating with analytics. The only thing matters is how well KNIME or Alteryx fits to our work environment.
I like the fact that in some data manipulation, alteryx uses 1 block to solve. where KNIME I have to use 3-5 steps to get that I wanted… But I like KNIME because it seems to be working with R better than Alteryx.
SO, dont mention what alteryx cost. when millions are on stake ! we use both KNIME and Alteryx. each of them have their own edge. its like Mac and Windows. it’s just a tool for us to do what we like to do.
whether we need a Porcha or Toyota, as long as it can get us there, safely and quickly, we dont give a dam. cost is not a factor…
@KNIMEuser23421
Yes I can see now that the column expression I posted doesn’t do the task because it cannot self refer to the column it is creating. That’s fine. I am big enough to admit when I made an error. Are you?
Can you please answer me the one simple question that is still bugging me. What did the X mean in your first question?
Oh and one other question. Are you always this angry?
X is a constant … not a dynamic number… can be anything…
prior table was wrong… I am uploading new one …
Ok, Gordon… I had kind of gathered it could be “anything”. I was vaguely wondering which anything it was, and where we were supposed to find it, but never mind.
And by the way I strongly disagree with your previous statement that @ArjenEx told you to search the forum and in any way disrespected you. He was pointing you at a post which suggested ways to write a good post. That generally involves supplying sample test data (as you subsequently did) .
Anyway…
So you want this cumulative loss calculated in a single KNIME node. OK. I know you said you don’t really know Java and looking back you did ask if it could be done with a java script, so here you are:
If you would like to understand how that works… see the following post.
I’m going to go ahead and close this thread for now. Several possible solutions have been provided but the discussion is a bit too heated. @KNIMEuser23421 , please take a bit of time to try some solutions out, and come back in a few days with a new thread if you are still stuck.
