replace null value with last previous value in the same column based on criteria

[jordanscraw]

I would like to update the null values in the following table to equal the last previous price in the table based on a couple of criteria.  For example, on record 2, where the WHS and RESC columns match, I would like to fill the value from the previous month (2002_10) into the month missing pricing data (2002_11).  How can I accomplish this in Alteryx?  

The values in the table below would be as updated to the following for the records which are currently null.

2. 0.00

4. 0.0357

5. 0.0357

7. 0.0472

[Thableaus · Accepted solution]

Hi @jordanscraw 

Use the Multi-Row Formula Tool.

Select - Update Existing Field

IF IsNull[Value] then [Row-1:Value]

ELSE [Value] ENDIF

Cheers,

[caschecter · Accepted solution]

I would add one thing to the answer given by @Thableaus and that is, being that WHS and RESC values can change, then you will want to set the "group by" feature of the  multi-row formula tool and check the WHS and RESC fields. This will make sure that the first value for a new WHS/RESC pair does not take the previous value from  the previous row, but starts over.

[jordanscraw]

Thanks, this worked perfectly.

[jordanscraw]

Caschecter,

Good observation.  I did need to group by the WHS and RESC values as you suggested.  That was an important last piece of this solution for me.  Thanks for following-up.

[carmen1]

Hi All!!

Hope this post finds you well! I am having trouble with this issue! I have a table and I would like to fill up  the  first non null value, however there more than one blank space in between. Do u have any idea to sort this out?

Best!!

[caschecter]

Carmen1,

Could you please mock up what you expect the output to look like.

[RKahle]

I've encountered a similar issue. I would like all the blanks in the attached input to be filled with the most recent previous price. I thought I could use a serious of Multi-Row Tools, but it doesn't solve the entire problem, especially where there are blanks prior to the EffectiveStartDate or multiple blanks in a row.

I think an Iterative MACRO approach may solve the issue, but am struggling with the logic.

[caschecter1]

RKahle, 

A multi-row Formula tool that Updates the existing field Settle Price with the following expression should resolve your issue.

if (isempty([SettlePrice])) THEN [Row-1:SettlePrice] ELSE [SettlePrice] ENDIF

Best of luck.

[RKahle]

Attached is the data input. I would like all the blanks to be populated with the most recent previous price, grouped by ID. 

That formula will work, but for only a few instances. If there are multiple blanks in a row, then it won't work.