topic New Column Set Value Help in Discussions

New Column Set Value Help

jy06115197 — Fri, 09 Jun 2023 22:03:49 GMT

I know the following script is incorrect:

a = {"a", "b", "b"};
b = [1, 2, 3];
c = [4, 5, 6];

New Table( "SQL Results",
    New Column( "a", character, set values( a ) ), 
    New Column( "b", formula( If( :a == "a", set values( b ), set values( c ) ) ) )
);

How do I change it to make the output:

a 1
b 5
b 6

so the first row checks if column 1 is "a", yes then use b[1]; second-row checks if column 1 is "a", no then use c[2]; third-row checks if column 1 is "a", no then use c[3].

Thank you!

Re: New Column Set Value Help

txnelson — Wed, 20 Jan 2021 18:43:09 GMT

Here is how to do what you want. Please note that JMP had an issue in the formula with the matrix b reference, since the formula was being interpreted as referencing the column b. That is why I changed the column name to b2.

Names Default To Here( 1 );

a = {"a", "b", "b"};
b = [1, 2, 3];
c = [4, 5, 6];


dt = New Table( "SQL Results",
	New Column( "a", character, set values( a ) )
);


dt << New Column( "b2",
	formula(
		If( :a == "a",
			b[Row()],
			c[Row()]
		);
	)
);

To illustrate the scoping issue further, if you really need the column to be named "b" then one can specify the fully scoped name of here:b (the "here" comes from Names Default to Here( 1 ) )

Names Default To Here( 1 );

a = {"a", "b", "b"};
b = [1, 2, 3];
c = [4, 5, 6];


dt = New Table( "SQL Results",
	New Column( "a", character, set values( a ) )
);


dt << New Column( "b",
	formula(
		If( :a == "a",
			here:b[Row()],
			c[Row()]
		);
	)
);

Re: New Column Set Value Help

jy06115197 — Wed, 20 Jan 2021 18:45:43 GMT

Nelson,

Thank you so much. I truly appreciate your help.