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
Solved! Go to Solution.
Hi @jordanscraw
Use the Multi-Row Formula Tool.
Select - Update Existing Field
IF IsNull[Value] then [Row-1:Value]
ELSE [Value] ENDIF
Cheers,
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.
Thanks, this worked perfectly.
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,
Could you please mock up what you expect the output to look like.
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.
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.