Replace value in one row with another based on condition

[taran42]

I am wanting to replace the value in one row with the value from a different row based on a certain condition.

For example, I want to replace the value 48,728 in the Total RSF column with the value 45,196 from the Project RSF column. So the formula would look something like if Tenure equals "Leased" and Project RSF is Null(), then I want to take the value in Project RSF and replace the value in Total RSF with it.

There are another 50 cities to do this for, which may have more or fewer records (if any records at all) and their figures will be different. Does anyone have any ideas on how I can accomplish this?

Edit: @vizAlter I created a sample file and attached it. What I want to do is if the Project RSF is Null() and the Tenure equals "Leased," then put the Project RSF value from the other row in the Null position.

So the blank area where the red box is, is where the Project RSF needs to be added.

[vizAlter]

@taran42 - Share some dummy records for speedy solution.

[pedrodrfaria]

HI @taran42 

I was not sure if I missunderstood it, but I attached below example of both. Are you trying to replace the Project RSF column or the Total RSF? As I said, I was not sure which one, so I added both and one of them should do the trick for you.

Here is the formula I believe you are looking for:

if [Tenure] = 'Leased' and [Project RSF] = null() then [Project RSF] else [Total RSF] endif 

if [Tenure] = 'Leased' and [Project RSF] = null() then [Total RSF] else [Project RSF] endif

Pedro.

[taran42]

@pedrodrfaria Thank you for your solution, but I am wanting to replace the Null value in Project RSF with the number from the other row. I have updated my original post with, hopefully, a better description.

[pedrodrfaria · Accepted solution]

Hi @taran42 

Would this be the solution you are looking for?

Pedro.

[taran42]

@pedrodrfaria Thank you very much for your help! With your solution I was able to find a fix for my problem. I used your workflow almost exactly. For some reason, when I added this to my regular flow, it would not union back any values other than Leased. I added a Join before the last Union and only unioned the left and right side. This gave me the results I needed.

[s_r_7]

Hi - would you be able to just list the steps you used in this workflow as i'm trying to recreate it but i don't have the same icons as you do.