Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

jordanscraw
6 - Meteoroid

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

 

jordanscraw_0-1576096780947.png

9 REPLIES 9
Thableaus
17 - Castor
17 - Castor

Hi @jordanscraw 

 

Use the Multi-Row Formula Tool.

 

Select - Update Existing Field

 

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

ELSE [Value] ENDIF

 

Cheers,

caschecter
6 - Meteoroid

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
6 - Meteoroid

Thanks, this worked perfectly.

jordanscraw
6 - Meteoroid

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
7 - Meteor

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
6 - Meteoroid

Carmen1,

 

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

RKahle
7 - Meteor

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_1-1677538175822.png

 

RKahle_0-1677538378729.png

 

 

 

caschecter1
6 - Meteoroid

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
7 - Meteor

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. 

Labels