Check order of occurence, ignoring repeatation and assign a value in new coloumn

I have a processed dataset that looks like this. The S.No column follows a certain order, with some values repeating as in the sample below.

I want checkand obtain a result, where if the the rows follow the sequence as in increasing order, 1-2-3-4-5- etc (repeatation ignored), then a new coloumn needs to be assigned with a value as “X” and if it follows otherwise, that is 5-4-3-2-1, then assign “Y”.
S. No Name
1 A
1 A
1 A
1 A
2 B
2 B
3 C
3 C
3 C
4 D
4 D
5 E
5 E

Hello @najath_ambalakk,

you can use Column Expressions node to get difference between current and next row with following expression:
column("yourColumnName") - column("yourColumnName", 1)

Write it as new column and then with Rule Engine you can check if value is in every row less or equal to zero then order is ascending and you can write “X”. If it’s higher or equal to zero then order is descending and you can write “Y”. Otherwise you don’t have order :wink:

Br,
Ivan

2 Likes

Hi @ipazin ,
Thank you for your answer.
This works pretty well!

Asking for a slight modification, if possible.
I see that there were some missing values in my coloumn with the series of numbers although still aligning the order.
When I apply the the col.expression operation as you suggested, its not giving me just negative values, but also positive vaues when the succeeding entry is a missing value…

image

How can the check be adjusted to incorporate the missing values qithout having to fill it with previous values/next values (using Missing value node) and nevertheless get the desired results?

Hello @najath_ambalakk,

I suggest to use Missing Value node as it’s much easier than coding this…

Br,
Ivan

Hi @ipazin,

I filled the missing values and doing the check, yet saw an issue with the logical check for the expression we defined here as the sequential values are repeating.

When we have, 1 1 1 2 2 3 3 3 3 3 4 4, and we do a check with -
column("yourColumnName") - column("yourColumnName", 1), any value with 0 and negative values show that it can be of increasing order.
But when we apply the same for 4 4 4 3 2 2 1 1 1 1, the expression will also be giving 0 and positive values. Meaning, the presence of 0’s not being exclusive creates a problem for assigning my desired value based on the check if its increasing or decreasing. This was not working for the data record later.
Hence, my idea is to use SUM check on the new coloumn obtained from col.expression.
If the sum is negative, assign value “X” and if the sum is positive, assign value “Y”


How would the rule engine entry would be then? I tried this:
$sum(new column0$) > 0 => “X”
$sum(new column0$) < 0 => "Y but it wasnt working. Is the syntax wrong?

Hello @najath_ambalakk,

I see. You can use Math Formula node to get sum in new column and then Rule Engine should work. Or you already tried it and it’s not working? If so please share some screenshot that can help or better workflow example…

Br,
Ivan

1 Like

Hello @ipazin ,
Once again thank you for your time.
I seemed to have sorted a way out of this problem. Although the sum function and the rule assignment wasn’t directly working with the " Rule engine" node alone as said, I tried a different approach.

  1. Similar to what you said, I used a “Math formula” to compute the coloumn sum (of the coloumn expression as you said) and then used the “rule engine” such that if the sum is negative, it means its an increasing order of sequence, and if the sum is positive, the sequence is decreasing. This gives me exclusive results as the data set contained millions of records and need to be run per each group using a group looping node.
  2. The other way I tried was using “Group by node”, grouped them and computed a col Sum. Then used a rule engine with same logic as before, and since the “Group By” node only gave the coloumn that I was grouping them into, I had to use a “cross joiner” again to bring the whole data back together.

Anyway, both works and seemingly first one looks cleaner with 1 lesser node.
Thank you for your simple yet logical ideas and inputs.
Best,
Najath

2 Likes

Hello @najath_ambalakk,

glad to hear you made it and you are welcome. Always happy to help other KNIME users and contribute to this Community.

Have a nice weekend,
Ivan

1 Like

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