Fill in Initial Postop

[Faronnj]

Goodmorning Alteryx Community,

I have the following data:

Last	Preop IOL Ref Sph	Postop 1-3mo Sph	> 4-12mo Sph	> 1-2yrs Sph	> 2-3yrs Sph	> 3-5yrs Sph	> 5-7yrs Sph	> 7-9yrs Sph	> 10yrs Sph
1	-9.50	0.00
2	-15.50			0.50
3	-15.50	0.50		0.25
4	-20.25					0.25
5	-15.00						0.75
6	-7.00							0.5
7	-5.00							0.25
8	-14.00		0.00		0
9	-15.00	0	0.00		0
10	-10.00	-0.50		0.00
11	-7.75		0	0
12	-7		-1	-0.5
13	-15			1	1
14	-12.75			0	0
15	-18.75		1.25		-1.5
16	-20.00		2.00		1.5
17	-15.50			-1.00
18	-10.25					0.75
19	-10.75	0.25

I want alteryx to create the following table but I dont know how to achieve this.  I want alteryx to fill in the postop 1-3mo period with the data from the future follow-ups that do have data.  However, if the 1-3mo period already has an outcome (value) in it, I dont want it to move the future follow-up data but leave it the same.  Sorry it is hard to explain but I hope you understand if you look at the 2 tables:

Last	Preop IOL Ref Sph	Postop 1-3mo Sph	> 4-12mo Sph	> 1-2yrs Sph	> 2-3yrs Sph	> 3-5yrs Sph	> 5-7yrs Sph	> 7-9yrs Sph	> 10yrs Sph
1	-9.50	0.00
2	-15.50	0.50		0.50
3	-15.50	0.50		0.25
4	-20.25	0.25				0.25
5	-15.00	0.75					0.75
6	-7.00	0.50						0.5
7	-5.00	1.00						0.25
8	-14.00	0.00	0.00		0
9	-15.00	0	0.00		0
10	-10.00	-0.50		0.00
11	-7.75	0	0	0
12	-7	-1	-1	-0.5
13	-15	1		1	1
14	-12.75	0		0	0
15	-18.75	1.25	1.25		-1.5
16	-20.00	2.00	2.00		1.5
17	-15.50	-1.00		-1.00
18	-10.25	0.75				0.75
19	-10.75	0.25

I would greatly greatly appreciate if you attach a workspace so I can see the formulas and everything I need to do to achieve this and so I can save it for reference since I have to do this for several data.  Thank you again in advance,

Nick 

[danilang · Accepted solution]

Here you go @Faronnj 

The trick here is transpose all the follow up columns so they end up in a single column.  After removing the blank rows, you copy the next row's value to the row if the 3 month value is blank.  Remove the non 3 month rows from the transposed data and join on Last.  Then you copy the new field into the 3 mo column, giving

BTW: Unless there's an extra rule that you didn't mention, the value for row 7 should be 0.25 as opposed to the 1 in your sample output.

Dan

[Faronnj]

No other rule, that was my mistake when I was filling in the data to show what I would like it to look like!  Thank you for all your help!

Nick