nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Saving Variables to New Tables and New Columns

SMW

I wrote a script that finds rows where col2 and col3 match specific strings and then returns the value of the cell in col7 of those rows. However, I am having problems writing these variables to New Columns in New Tables.

....
New Table( "Results",Add Rows( 2 ),  
  New Column( " FUS",Character,"Nominal",  
    Set Values({"Fus1","Fus2"})),  
  New Column( "PASS/FAIL",continuous,"Nominal", formula( Name Expr(APass)),formula(Name Expr(BPass)));  
 )

Here, APass and Bpass are variables that equal the column7 value from respective search rows. They are strings Pass and Fail respectively. However, when I create the table the last function overides and fills in its results across the entire table. So the table should be Fus1-Pass Fus2-Fail, but outputs Fus1-Fail Fus2-Fail. The reverse when I change the order.

Any help on how to populate a new column with multiple variables ?

Much Appreciated

Accepted answers

txnelson

As stated in your previous discussion on this topic, You CANNOT have to formulas defined for a column. Here is a simple illustration of that using the Big Class data table. If you define 2 formulas for a column, the second formula will replace the first formula.

new column("two formula", formula(:height), formula(:weight));

Gives the following results

Note that the second formula defined is the one JMP ends up using.

Now, this is not saying that a formula can not be complex. One can actually define a formula that treats each row differently. It just has to be defined in one formula. Here is a simple example of a more complex formula that takes multiple columns into the formula, and makes comparisons between them.

 New Column( "combined formula",
	formula(
		If(
			:Sex == "F" & :age > 12, :weight,
			:Sex == "F" & :age <= 12, :height,
			:Sex == "M" & :age > 13, :weight,
			:Sex == "M" & :age <= 13, :height
		)
	)
)

pmroz

I provided this response to SMW privately; hopefully it will help others.

Try running this code. Look in the log window to see what happens. The get rows where function returns a matrix, not an actual row number. You have to loop over the matrix to use the row values.

Names Default To Here( 1 );
dt1 = New Table( "Test", Add Rows( 4 ),
	New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4] ) ),
	New Column( "Column 2", Character, "Nominal",
		Set Values( {"This is EMOTIONAL", "abc", "EMO", "Here's an EMO example"} ) ),
	New Column( "Column 3", Character, "Nominal",
		Set Values(
			{"And not KLARIFIED", "KLA", "xyz", "This contains KLA string"} ) ),
	New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 7", Character, "Nominal",
		Set Values( {"Hello", "World", "Fubar", "Ski"} ) ),
);

match_rows = dt1 << get rows where(contains(as column(2),"EMO")
								 & contains(as column(3),"KLA"));

for (i = 1, i <= nrows(match_rows), i++,
	one_row = match_rows[i];
	apass   = column(dt1, 7)[one_row];
	print(apass);
);

All comments

txnelson

new column("two formula", formula(:height), formula(:weight));

Gives the following results

Note that the second formula defined is the one JMP ends up using.

 New Column( "combined formula",
	formula(
		If(
			:Sex == "F" & :age > 12, :weight,
			:Sex == "F" & :age <= 12, :height,
			:Sex == "M" & :age > 13, :weight,
			:Sex == "M" & :age <= 13, :height
		)
	)
)

pmroz

I provided this response to SMW privately; hopefully it will help others.

Try running this code. Look in the log window to see what happens. The get rows where function returns a matrix, not an actual row number. You have to loop over the matrix to use the row values.

Names Default To Here( 1 );
dt1 = New Table( "Test", Add Rows( 4 ),
	New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4] ) ),
	New Column( "Column 2", Character, "Nominal",
		Set Values( {"This is EMOTIONAL", "abc", "EMO", "Here's an EMO example"} ) ),
	New Column( "Column 3", Character, "Nominal",
		Set Values(
			{"And not KLARIFIED", "KLA", "xyz", "This contains KLA string"} ) ),
	New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., .] ) ),
	New Column( "Column 7", Character, "Nominal",
		Set Values( {"Hello", "World", "Fubar", "Ski"} ) ),
);

match_rows = dt1 << get rows where(contains(as column(2),"EMO")
								 & contains(as column(3),"KLA"));

for (i = 1, i <= nrows(match_rows), i++,
	one_row = match_rows[i];
	apass   = column(dt1, 7)[one_row];
	print(apass);
);

SMW

Thank you for taking the time to explain the issue. Im very new to JSL scripting and I appreciate you taking the time to examine how a new column can be a complex formula.

SMW

Thanks again for the help pmroz.

Boiled down, the problem is as simple as finding rows that contain specific text in two columns. Then creating a new table with those rows and the corresponding value in column x of that row. But it got really complicated using the wrong functions.

SMW

One side question. If the data headers for the columns are simply column 1, column2, column 3....., how does the scoping syntax work .

If(
:Column2 =="text" & :Column3 =="text"

//Or would you use 
if(
:col[2] =="text" & :col[3]=="text"

//I'm a bit confused about the syntax in a situation where my output files headers
are not formatted very nicely. Specifically, they start at row 7.  I was trying to get around having to modify these tables by simply referencing the column #s

if you have any insight, thank you in advance.