fill missing data with conditions

walk545 — Sun, 03 Nov 2019 07:40:57 GMT

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.

Re: fill missing data with conditions

ron_horne — Sun, 03 Nov 2019 11:55:20 GMT

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));

let us know if this helps.

Ron

topic fill missing data with conditions in Discussions

fill missing data with conditions

Re: fill missing data with conditions