Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
walk545
Level II

fill missing data with conditions

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:

nameChange Shirt DateNext change shirt dateshirt color
Jay10/10/2019 22:1910/15/2019 18:49Yellow
Tom10/13/2019 6:0910/13/2019 18:25brown
Tom10/13/2019 18:2510/16/2019 5:56na
Jay10/15/2019 18:4910/18/2019 16:05na
Tom10/16/2019 5:5610/20/2019 2:31na
Jay10/18/2019 16:0510/22/2019 3:17na
Tom10/20/2019 2:3110/23/2019 18:41na
Jay10/22/2019 3:1710/25/2019 5:44na
Tom10/23/2019 18:4110/26/2019 21:14na
Jay10/25/2019 5:4410/28/2019 9:10na
Tom10/26/2019 21:1410/29/2019 2:02na
Jay10/28/2019 9:1010/31/2019 0:51Green
Tom10/29/2019 2:0211/2/2019 3:13gray
Jay10/31/2019 0:5111/3/2019 6:57brown
Tom11/2/2019 3:1311/3/2019 6:57na

thank you to anyone who helps.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ron_horne
Super User

Re: fill missing data with conditions

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

View solution in original post

1 REPLY 1
Highlighted
ron_horne
Super User

Re: fill missing data with conditions

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

View solution in original post

Article Labels

    There are no labels assigned to this post.