Column Expression check if all elements are equals

[Zarkoff95]

Hi,

I really like playing with column expression node… but as I don’t know Javascript, it’s sometime very cryptic for me

I’ve 3 columns with price.
I want to display a message if no price is defined, or if prices are defined but not aligned together or if prices are all aligned.

I wrote the below code but it doesn’t work as expected when there are null values.
Also, sometimes it returns “Aligned” while price are different.
Also it would only work with 3 prices, and I would like to make it more flexible if one day I need to.

Price can be like this
lp1 lp2 lp3
1 ? ? => Only 1 price is defined
2 2 ? => Price are algined
? 2 ? => Only 1 price is defined
? ? 3 => Only 1 price is defined
4 ? 5 => Price are not aligned
7 7 8 => Price are not aligned

Code I tried

LP1 = column("lp1")
LP2 = column("lp2")
LP3 = column("lp3")

arr=arrayCreate(LP1,LP2,LP3)
arr=arrayRemoveElements(arr, null)
arr=arrayRemoveElements(arr, null)

len = arrayLength(arr)

if (len=1){
    "Only one Price is defined"}

if (len=3){
    if ((arrayIndexOf(arr,0 ) == arrayIndexOf(arr,1 )) && (arrayIndexOf(arr,0 ) == arrayIndexOf(arr,2 )) && (arrayIndexOf(arr,1 ) == arrayIndexOf(arr,2 ))) {
        "Price(s) Aligned"
    } else {
        "Price(s) are not Aligned"
    }
}

if (len=2){
    if ((arrayIndexOf(arr,0 ) == arrayIndexOf(arr,1 ))) {
        "Price(s) Aligned"
    } else {
        "Price(s) are not Aligned"
    }
}

if (len=0){
    "No Price is defined"}

Thank you

[ArjenEX]

Hi @Zarkoff95

The Column Expression is indeed a very powerful tool, but like you have experienced can get a bit of a trap when dealing with scenario’s like this.

To make this dynamic, I would opt for a more straight forward rule set.

I start with a Column Aggregator nodes that captures all LP columns. Whenever more are added, these are automatically taken into account.

Next I determine the range and count of all values within the set column scope.

Note: disable the Missing checkmark to disregard missing values.

I use these values to analyze in a Column Expression:

if (column("Count") == 0) {
    "No price is defined"
} else if (column("Count") == 1) {
    "Only 1 price is defined"
} else if (column("Range") >= 1) {
    "Price are not aligned"
} else {
    "Price are aligned"
}

Rule 1: If the count is zero, no value was found within any of the columns.
Rule 2: If the count is one, only one individual value was found within any of the columns.
Rule 3: If the range is higher or equal to 1, that automatically means that different numbers are present within the set of columns (equal numbers equals range = 0). Subsequently, this satisfies the rules that the prices are not aligned.
Rule 4: If non of the above conditions are met, the price is aligned.

This generates the output in accordance with your description:

Lets assume a 4th column is now data with this dummy data set:

The script assigns the correct output without further invertention or manual coding required:

See WF:
Column Expression check if all elements are equals.knwf (25.0 KB)

Hope this provides some inspiration!

[Zarkoff95]

@ArjenEX, thank you, it works like a charm.

And thank you for this very detailed explanation, you are right, it will inspire me a lot.

I tried to do it entirely in Column expressions and this is probably why I failed.

What I learned here is that it’s sometimes easier to do it in several node than doing it with only one.

I just did a modification on the 5th line
else if (column(“Range”) != 0) instead of else if (column(“Range”) >= 1)

if (column("Count") == 0) {
    "No price is defined"
} else if (column("Count") == 1) {
    "Only 1 price is defined"
} else if (column("Range") != 0) {
    "Price are not aligned"
} else {
    "Price are aligned"
}

[Daniel_Weikert]

Can anyone add to the column expression node performance here? The node is flexible but I am unsure whether this node is actually a good fit cosidering performance in comparison to other nodes (here rule engine for example)
thanks

[ArjenEX]

Are you looking for a benchmark between Rule Engine and Column Expression in this case?

[Daniel_Weikert]

Not specifically this case. It is a general question about column expression’s performance
br

[takbb]

I agree @Daniel_Weikert. I would also be interested to know if the Column Expressions node performance has improved in recent releases.

I generally avoid it both because writing code increases the testing and documentation burden and because it has historically been relatively slow in execution. That said, for some use cases it serves a purpose so I don’t rule it out completely.

[Daniel_Weikert]

@ScottF Can you or a Team member elaborate on the performace question for us? Thanks and take care