Traversing rows without loop

[bao_nguyen]

Hello,

I am trying to achieve this task:

• Create a new column called 'color':
  • Starting at the max/highest index and traversing backward to the lowest index with these rules 
    1. If current value > A
       1. set color red
       2. move next
    2. Else, If current and two previous values > B,
       1. set color orange for all three rows,
       2. skip the next 2 rows
    3. Else, If current and 4 previous values > C,
       1. set color yellow for all 5 rows,
       2. skip the next 4 rows
    4. Else, set color black, move next

Rule 1 has higher priority than Rule 2, and so on.

I am able to achieve the task but it's involved creating multiple extra columns just for one column. This is not ideal because I will have at least 20 columns.

Any input or help is greatly appreciate!

Thanks!

 

B

[Daniel29195]

@bao_nguyen 

please share simple sample data with the desired output . 

best regards