topic Re: Saving Variables to New Tables and New Columns in Discussions

Saving Variables to New Tables and New Columns

SMW — Fri, 09 Jun 2023 19:58:52 GMT

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

Re: Saving Variables to New Tables and New Columns

txnelson — Wed, 06 Oct 2021 21:36:53 GMT

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
		)
	)
)

Re: Saving Variables to New Tables and New Columns

pmroz — Thu, 07 Oct 2021 19:00:36 GMT

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);
);

Re: Saving Variables to New Tables and New Columns

SMW — Fri, 08 Oct 2021 23:10:27 GMT

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.

Re: Saving Variables to New Tables and New Columns

SMW — Fri, 08 Oct 2021 23:15:35 GMT

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.

Re: Saving Variables to New Tables and New Columns

SMW — Fri, 08 Oct 2021 23:21:03 GMT

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.