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 |
… | … | … | … |
Solved! Go to Solution.
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 |
@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.
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
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.
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.