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:
thank you to anyone who helps.
Go to 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.
View solution in original post
There are no labels assigned to this post.