fill missing data with conditions

[#1]

I have a dt that has missing data that i need to fill from the same column.

the table tells us the color shirt someone is wearing i have a name, the date he changed his shirt, the next date he will change his shirt, and the color of the shirt.

my problem is that he doesnt change his shirt everytime he updates his data. if he doesnt change his shirt he writes "na".

i need to switch all the "na" with the color of the last time he changed his shirt. i need to fill the color conditioned by it being the same person and it being the last color recorded by him.

here is the table:

name	Change Shirt Date	Next change shirt date	shirt color
Jay	10/10/2019 22:19	10/15/2019 18:49	Yellow
Tom	10/13/2019 6:09	10/13/2019 18:25	brown
Tom	10/13/2019 18:25	10/16/2019 5:56	na
Jay	10/15/2019 18:49	10/18/2019 16:05	na
Tom	10/16/2019 5:56	10/20/2019 2:31	na
Jay	10/18/2019 16:05	10/22/2019 3:17	na
Tom	10/20/2019 2:31	10/23/2019 18:41	na
Jay	10/22/2019 3:17	10/25/2019 5:44	na
Tom	10/23/2019 18:41	10/26/2019 21:14	na
Jay	10/25/2019 5:44	10/28/2019 9:10	na
Tom	10/26/2019 21:14	10/29/2019 2:02	na
Jay	10/28/2019 9:10	10/31/2019 0:51	Green
Tom	10/29/2019 2:02	11/2/2019 3:13	gray
Jay	10/31/2019 0:51	11/3/2019 6:57	brown
Tom	11/2/2019 3:13	11/3/2019 6:57	na

thank you to anyone who helps.

[#2 · Accepted solution]

Hi @walk545 ,

Have a look at this solution. it is a bit low-tech but you should be able to get the result you want. just open the attached file and run the following script on it.

Names Default To Here( 1 );
dt = current data table ();

dt << New Column( "Row", formula( :Row() ), eval formula );
dt:row << delete formula;

dt << Sort( replace table,
	By( :name, :Change Shirt Date, :Next change shirt date ),
	Order( Ascending, Ascending, Ascending ),

);

for each row ( if (:shirt color == "na", :shirt color = lag (:shirt color, 1)) );

dt << sort (replace table, By (:Row), order (Ascending));

A similar issue was addresses in this post https://community.jmp.com/t5/Discussions/help-creating-a-column-that-is-a-cumulative-sum-based-on-ot... 

let us know if this helps.

Ron