Substracting values between many columns - Loop?

Hi,
first I w
Test for knime hub.xlsx (10.4 KB)
ant to wish a happy new year.

I have an excel sheet with data of different dates. The data should be substracted between different columns. This could be a problem of a nested loop.
The main procedure is to substract values between columns so that you get in the result table the result of the difference. The practice is in the first step to substract between column 2 and 1, then between column 3 and 1 and then between column 4 and 1. Then you move one column further so that you move in the second step to substract between column 3 and 2 and column 4 and 2. And in the third step to substract between column 4 and 3. Could be a node like column expression and probably with nested loops, but i couldn’t find the right code for nested loop in this node.
Hint this will later be a table with more than 100 rows and columns. There are also missing values like in the last row.
An example is shown below in the uploaded excel file.
Thanks for responding.

Hi,
You can use code like this in the Column Expression node:

var a = arrayCreate(0)
for (var j = 2; j <= 4; j++)
    for (var i = j; i <= 4; i++)
        a = arrayAdd(a, column(i) - column(j - 1))
arraySubset(a, 1, arrayLength(a))

For the output, you need to check the “Collection” checkbox and after this node, you need to unwrap the array you create in the code using a Split Collection Column node.
The code first initializes an array with a 0 in it because there is no method to create an empty array, then it goes through the nested loops to generate the computed values. In the end the initial 0 is removed using the arraySubset function.
Kind regards,
Alexander

4 Likes

Hi Alexander,

this nodes working pretty well. Thanks a lot. Only the little problem in the last row. When there are missing values, then the code reads this missing values as zero values. Can I bypass this problem with a kind of data manipulation? The idea is when there is a missing value, then no substracting should took place, because there is no value.
Thanks for responding !!

Hi,
Yes, for this you need to add a check like this:

var a = arrayCreate(0)
for (var j = 2; j <= 4; j++)
    for (var i = j; i <= 4; i++)
        if (isMissing(column(i)) || isMissing(column(j - 1)))
            a = arrayAdd(a, null)
        else
            a = arrayAdd(a, column(i) - column(j - 1))
arraySubset(a, 1, arrayLength(a))

Kind regards,
Alexander

Thanks for your pretty good and fast answers. Last question is the code written in java or java script, because there are no semi colons at the end of the line and also no curly braces, what makes it easier to write and understand the code. Or ist only a advantage in this code column expression?

Kind regards

Hi,
The Column Expression uses JavaScript. So you could add semcolons and curly braces. Semicolons are optional in JavaScript, curly braces are only necessary for blocks with multiple statements.
Kind regards,
Alexander

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.