You now have until September 15th to submit your use case or success story to the 2022 Dataiku Frontrunner Awards!ENTER YOUR SUBMISSION

Multi row formula

Solved!
nkt
Level 2
Multi row formula

Hi all,

I have the following dataset (in real life 100+ rows):

Column AValue
1100
280
385
482
570

 

I would like to correct the Values column so it is always decreasing, taking the value of the previous row if the value has increased. So the table should be corrected to this:

Column AValue
1100
280
380
480
570

 

To achieve this I would like to use the formula targeting multiple rows:

if (Value] > [Row-1:Value]) AND !isnull([Row-1:Value]) then [Row-1:Value] else [Value] endif

How can I achieve this?

I tried the window recipe to determine the lag difference (difference with the previous row), remove the positive lag difference values and fill the empty cells with the previous value. However, this only works when you look 1 row back, so the example row #4 would not be corrected. I can look back multiple rows, however this generates also multiple columns in the windows recipe. For a dataset with 100+ rows, this means 100+ additional columns. This doesn't feel like the best method.

Hopefully you know a better method!

Thank you in advance.

Best,

Nick

0 Kudos
1 Solution
nkt
Level 2
Author

Solved this by using the Window recipe, sorting on Column A and selecting the Min of Value. Make sure you do not select the Window frame option (in the Windows definition screen).

View solution in original post

1 Reply
nkt
Level 2
Author

Solved this by using the Window recipe, sorting on Column A and selecting the Min of Value. Make sure you do not select the Window frame option (in the Windows definition screen).