topic Replacing a blank row with another row data with an if statement in Discussions

Replacing a blank row with another row data with an if statement

kuanaunwei — Thu, 08 Jun 2023 16:43:43 GMT

Hi,

I have an original set of data (table1), and I am trying to script it to the result in table 2.

If any number that is below 300, will print "type1" string into "result1" column.

If any number is above 300, will print "type2" string into "result2" column.

Number	type1	type2	result1	result2
100	a1	a2
200	b1	b2
150	c1	c2
275	d1	d2
555	e1	e2
424	f1	f2
572	g1	g2
783	h1	h2
125	i1	i2

Table1: Original data

Number	type1	type2	result1	result2
100	a1	a2	a1
200	b1	b2	b1
150	c1	c2	c1
275	d1	d2	d1
555	e1	e2		e2
424	f1	f2		f2
572	g1	g2		g2
783	h1	h2		h2
125	i1	i2	i1

Table2: Result

So far, what i have done on my script is shown below.

dt = Current Data Table();
dt << New Column("result1",
	Character,
	"Nominal",
	For Each Row(If(:Number << 300), :result1 == :type1, "")
);
dt << New Column("result2",
	Character,
	"Nominal",
	For Each Row(If(:Number >> 300), :result2 == :type2, "")
);

Re: Replacing a blank row with another row data with an if statement

jthi — Wed, 11 Jan 2023 03:37:13 GMT

There are couple of issues, using >>/<< as comparison and using For Each Row inside column creation. Below is one option for working syntax using << Set Each Value (do not that if value is is exactly 300 it will be empty)

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(9),
	Compress File When Saved(1),
	New Column("Number", Numeric, "Nominal", Format("Best", 9), Set Values([100, 200, 150, 275, 555, 424, 572, 783, 125])),
	New Column("type1", Character(16), "Nominal", Set Values({"a1", "b1", "c1", "d1", "e1", "f1", "g1", "h1", "i1"})),
	New Column("type2", Character(16), "Nominal", Set Values({"a2", "b2", "c2", "d2", "e2", "f2", "g2", "h2", "i2"}))
);

dt << New Column("result1", Character, "Nominal", << Set Each Value(
	If(:Number < 300,
		:type1
	,  //else
		""
	)
));

dt << New Column("result2", Character, Nominal, << Set Each Value(
	If(:Number > 300,
		:type2
	,  //else
		""
	)
));

I strongly suggest checking out scripting index directly from JMP's Help menu and Scripting Guide from JMP Help Page . There are also other scripting sources such as

to get to know the syntax

Re: Replacing a blank row with another row data with an if statement

kuanaunwei — Wed, 11 Jan 2023 06:07:40 GMT

Hi jthi, Alright will tryout on the help info. The script works. Thanks for the help.

Re: Replacing a blank row with another row data with an if statement

txnelson — Wed, 11 Jan 2023 12:58:31 GMT

I personally find that approaching issues like this using an Excel like approach, where the new columns are created with individual formulas applied to each column is confusing and inefficient. JMP's capability to directly modify data table values in "open" JSL code can be used to solve the problem in a cleaner and more efficient manner.

// Create the new columns
dt << New Column( "result1", Character );
dt << New Column( "result2", Character );

// Populate the columns
For Each Row( If( :Number < 300, :result1 = :type1, :result2 = :type1 ) );