Generate Missing Dates / Rows with Previous Row Pricing Data

[RKahle]

Data Query pulling all historical prices for ID: 1234 for the previous 30 days. Only (1) price was submitted for this ID on 2/1/2023. I would like Alteryx to generate rows #2 - #13, grouped by ID as there are multiple different ID’s in the actual data set, and then replace rows #2 - #13 with the data of the most recent previous row, (Row #1) so that there is (1) price for each day.

I am wondering if a QueryStart (Today - 30 days) and Queryfinish (Today) field is necessary to then get the Date difference between Effective Date and QueryStart dates.

Row #	Effective Date	ID	Price
1	2/1/2023	1234	$2.00

Row #	Effective Date	ID	Price
1	2/1/2023	1234	$2.00
2	2/2/2023	1234	$2.00
3	2/3/2023	1234	$2.00
4	2/4/2023	1234	$2.00
5	2/5/2023	1234	$2.00
6	2/6/2023	1234	$2.00
7	2/7/2023	1234	$2.00
8	2/8/2023	1234	$2.00
9	2/9/2023	1234	$2.00
10	2/10/2023	1234	$2.00
11	2/11/2023	1234	$2.00
12	2/12/2023	1234	$2.00
13	2/13/2023	1234	$2.00
…	…	…	…

[binuacs]

@RKahle One way of doing this

[RKahle]

Thank you. However, I believe the solution is a bit more complex. Attached is my current workflow. A snip of the output table is shown below. I would like the blank prices to be filled in with the most recent previous day per PriceMasterID. 

EffectiveDate	PriceMasterID	SettlePrice
2023-02-03	14709	2.5085
2023-02-03	62746
2023-02-03	62747
2023-02-03	62808	2.316
2023-02-03	62808	2.311
2023-02-03	62808	2.3135
2023-02-03	62808	2.316
2023-02-03	62808	2.316
2023-02-03	62808	2.316
2023-02-03	62809	2.5585
2023-02-03	62809	2.5535
2023-02-03	62809	2.556
2023-02-03	62809	2.5585
2023-02-03	62809	2.5585
2023-02-03	62809	2.5585
2023-02-04	8464
2023-02-04	9572
2023-02-04	12138
2023-02-04	13872
2023-02-04	14709
2023-02-04	62746
2023-02-04	62747
2023-02-04	62808
2023-02-04	62809
2023-02-05	8464
2023-02-05	9572
2023-02-05	12138
2023-02-05	13872
2023-02-05	14709
2023-02-05	62746
2023-02-05	62747
2023-02-05	62808
2023-02-05	62809
2023-02-06	8464	2.7062
2023-02-06	8464	2.7037

[binuacs]

@RKahle In your input file all the settle prices are filled? I am a bit confused about the ask. would you be able to give more clarity on your requirement? also if possible provide the expected result as well.

[RKahle]

The below thread accurately describes my 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.

Solved: Re: replace null value with last previous value in... - Alteryx Community

[binuacs]

@RKahle One more question what is the expected value for the below case? is it 2.6473?

[RKahle]

For 62746 it would be Row #86 in the Input File, or 2.4763 posted on 2-1-2023. 

For 62747 it would be Row #87 in the Input File, or 2.7438 posted on 2-1-2023. 

[binuacs]

@RKahle I created the workflow. Can you check whether the results are expected or not?

[RKahle]

That formula will work for some of them, but if there are multiple blanks in a row of the same ID, the If Statement will terminate the loop. Also, what if there are blanks with a Price Date before the Price Date with a value? The formula would need to be Row+1 in that scenario. 

If it were possible, in the below instance, all the blanks would output 2.7933. 

[binuacs]

@RKahle I do see values for 62748 filled. Am I missing something?