topic Re: script for auto column filling in Discussions

script for auto column filling

qspringleaf — Fri, 09 Jun 2023 23:38:57 GMT

one question about script for column filling. now have one table for a lot kinds of fruit on sale, and a list includes fruit name that sold out already.

how to new create one column (name "sold out?"), and then judge by checking whether the fruit name match with the "sold out fruit list" (apple/durain/…sold out already), if yes, fill in "Y", if no, fill in "N", thanks a lot.

Fruit Name	sold out?
Apple	Y
Banana	N
durain	Y
pear	N
orange	N

Re: script for auto column filling

txnelson — Tue, 29 Sep 2020 08:16:57 GMT

Here is a solution using a JMP List as the input for the sold out fruit. I also convert the new column to static values by deleting the formula at the end, since if you save the data table, and then reopen it, the column will fail if a JMP list by the name of soldList is not present

Names Default To Here( 1 );
dt = New Table( "Fruit",
	New Column( "Fruit Name",
		character,
		values( {"Apple", "Banana", "Durain", "Pear", "Orange"} )
	)
);

soldList = {"Apple", "Durain"};

dt << New Column( "Sold Out",
	character,
	formula( If( N Rows( Loc( soldList, :Fruit Name ) ), "Y", "N" ) )
);
dt:Sold Out << delete formula;

Re: script for auto column filling

ThuongLe — Tue, 29 Sep 2020 06:24:44 GMT

or you can use Contains keyword to check. I'm using same example code from JIM

Names Default To Here( 1 );
dt = New Table( "Fruit",
	New Column( "Fruit Name",
		character,
		values( {"Apple", "Banana", "Durain", "Pear", "Orange"} )
	)
);

soldList = {"Apple", "Durain"};

dt << New Column( "Sold Out",
	character,
	formula( If( contains(soldList, :Fruit Name), "Y", "N" ) )
);
dt:Fruit Name << delete formula;