I have been struggling to write the function in column expression node using nested if statement on different columns and adding the result in new column.
Please find below the statement i am using. It runs individual nested if but does not add those all together. Please advice as what is the best node to use.
I am new to knime and am not familiar with Java and is limited to use nodes only. The data i have has roughly 1 million rows and 220 columns to extract the value and add those up:
if (column(“Test1-Mode)”)==0) {
column(“Test1-Max)”)}
else if (and (between (-1,column(“Test1+Skewness”) , 1)), column (“Test1+Kurtosis”)<3) {
column(“Test1-Mean”)}
else {column(“Test1-Median”)
}
+
if (column(“Test2-Mode)”)==0) {
column(“Test2-Max)”)}
else if (and (between (-1,column(“Test2+Skewness”) , 1)), column (“Test2+Kurtosis”)<3) {
column(“Test2-Mean”)}
else {column(“Test2-Median”)
}
+
if (column(“Test3-Mode)”)==0) {
column(“Test3-Max)”)}
else if (and (between (-1,column(“Test3+Skewness”) , 1)), column (“Test3+Kurtosis”)<3) {
column(“Test3-Mean”)}
else {column(“Test3-Median”)
}
Hi @Mobihashim01 , in addition to some sample data, as @lelloba has suggested, when uploading source code to the forum, it is best to highlight it and press the “preformatted text” button.
Otherwise, we don’t see the actual code, but a potentially corrupted version, so there is no way we can be sure what is wrong with it if it.
Additionally, it would be best to tell us in words what the logic is that you are trying to achieve. If we are to assume that there is something wrong with the logic in the code, then we cannot be sure what is intentional and what isn’t.
Finally, the sample data in this case would be useful specifically because it would also show us what your column names are.
When I look at the code you have uploaded, you appear to have column names such as Test1-Max) and Test2-Max) so these names have a single closing parenthesis in them? Now it might be that this is correct or it might be a typo, or possibly that what we are seeing is simply a corruption in what is posted here, caused by not marking the text as “preformatted”.
With Column Expressions, you can use variables, so rather than trying to produce a single long concatenation that can be difficult to follow and debug, you may find it more readable to use this format:
output="" /* define a variable output as an empty string */
if (column("ColumnA")==0)
{
output += column("ColumnX")
}
else if (column("ColumnA")==1)
{
output += column("ColumnY")
}
else
{
output += column("ColumnZ")
}
if (column("ColumnB") == 0)
{
output += column("ColumnN")
}
else if (column("ColumnB") == 1 )
{
output += column("ColumnP")
}
else
{
output += column("ColumnQ")
}
output /* return the value of "output" variable */
(@Mobihashim01 , I see you’d replied re the sample data while I was writing )
Based on your sample data, the following should work I think. I had to change some a number of the column names, and correct the positioning of some of the closing parentheses on the and( ) statements. Also, I had to remove spaces between the function names and the opening parentheses.
Instead of output being a string in my example code, it is now numeric for your data of course.
output=0
if (column("Test1-Mode")==0)
{
output+=column("Test1-Max")}
else if (and(between(-1,column("Test1-Skewness") , 1), column("Test1-Kurtosis")<3))
{
output+=column("Test1-Mean")}
else
{
output+=column("Test1-Median")
}
if (column("Test2-Mode")==0)
{
output+=column("Test2-Max")
}
else if (and(between(-1,column("Test2-Skewness") , 1), column("Test2-Kurtosis")<3))
{
output+=column("Test2-Mean")
}
else
{
output+=column("Test2-Median")
}
if (column("Test3-Mode")==0)
{
output+=column("Test3-Max")}
else if (and(between(-1,column("Test3-Skewness") , 1), column("Test3-Kurtosis")<3))
{
output+=column("Test3-Mean")
}
else
{
output+=column("Test3-Median")
}
output
the single parenthesis is type. As i tried to change the column headings due to the data security.
My problem is as follows:
I have vast amount of data and each record have three segments. i have calculated Min, Max, Mean, Medium, Mode, Standard Deviation, Skewness and Kurtosis for each segment.
I want to assign one value to each record. Based on mode value, and based on its skewness and kurtosis want to select what value to take for each segment among Mean, medium and mode and add those up.
For each segment
If mode is zero than Max value,
else if Skewness is between -1 and 1 and Kurtosis is less than 3
then mean value else Median
Based on above said selecting criteria, add the values of all three segments in new column.
Hi @Mobihashim01 , I edited my earlier response while you were writing the above. Hopefully that gives what you need
(You may need to adjust the column names, as there are differences between your original script and the example data so I don’t know which was correct.)
The output to the new column is the final calculation in the Column Expression. In this case the value of the variable “output”.
This variable starts at 0 and in each condition that is met, it adds the value in the required column to the current value of the “output” variable, using += syntax…
So it means output is set to output + value in each case. I hope that helps.
@takbb the column expression worked perfectly. Thank you very much. Much appreciated.
Now the next question is if i have say 10 variables (Test1 …Test10) and each variable has its own statistical parameters (Min, Max, mean,media, mode etc). I want to apply the same logic to each variable to select the value, how can i do this using one node? Or do i have to one column expression for each variable?
Hi @Mobihashim01 , there can be many “variables” within a single Column Expression script. It is basically a piece of javascript-based code and you can perform a variety of calculations using any number of variables. However the restriction is that a single piece of code within a Column Expression can only generate a single column of output.
You can of course within a single Column Expression node have it generate multiple column outputs but each of those runs as an independent script and executes in isolation from any of the other scripts within the one node.
I’m not sure if that answers your question, but in terms of variables (e.g. the “output” variable that I created in the earlier script), there is no practical restriction in how many you can use within the one script.
Now, looking at your data, you appear to have standardized names for all of your tests, and whilst it’s ok writing it out long-hand for a small number of tests, this approach doesn’t “scale” (and is boring/error prone to write) if you have a much larger number of these tests. What you could do is replace all that repetition of “boiler plate” script with a loop:
testResult=0
numberOfTests=3 // how may TestN columns are there?
/* loop through the test columns */
for(testNum=1; testNum<=numberOfTests; testNum++)
{
testName="Test"+testNum // e.g. Test1, Test2, Test3...
// define the names of the columns for this test...
testMode=testName+"-Mode" // e.g. Test1-Mode, Test2-Mode...
testMax=testName+"-Max"
testKurtosis=testName+"-Kurtosis"
testMean=testName+"-Mean"
testMedian=testName+"-Median"
testSkewness=testName+"-Skewness"
/* perform the calculation for this test */
if (column(testMode)==0)
{
testResult+=column(testMax)}
else if (and(between(-1,column(testSkewness) , 1), column(testKurtosis)<3))
{
testResult+=column(testMean)
}
else
{
testResult+=column(testMedian)
}
}
/* output the result */
testResult
The above code would work for tests number 1 to 3, but if you have 20 tests, all using the same column naming convention simply replace numberOfTests=3
with numberOfTests=20
thank you. This is fine but i was looking for if i can generate the results (multiple outputs) in multiple new columns which i understand is not possible and i have to have seperate column expression nodes for each new column i want. I hope my understanding is correct
Hi @Mobihashim01 , you don’t need separate Column Expressions nodes for this, but you do need to have each output column defined by a separate script within the one Column Expressions node
)
