New Column Set Value Help

[#1]

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!

[#2 · Accepted solution]

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

[#3]

Nelson,

Thank you so much. I truly appreciate your help.