Comparing rows and columns with special characters

[#1]

This might be very easy but I am new to JMP and finding it difficult to find a solution to this error.

Unexpected "%". Perhaps there is a missing "," or ")".

Scoped data table access requires a data table column or variable in access or evaluation of dt1: Acidic Variant' , dt1:Acidic Variant /*###*/

I am simply comparing a row in one table with columns of the other table. It does work for row and column names which do not have special characters like % or () but I will have names which will have special characters. Can anyone point out to me how this can be handled? Any help much appreciated. Thank you

[#2 · Accepted solution]

Here is a modification to your script that will allow it to work.  You were heading down the correct path, you just needed to use the :Name() function in the Substitute function

Names Default to Here(1);
dt1 = Data Table( "Example1" );
dt2 = Data Table( "Example2" );

a = N Rows( dt2 );
colNamesList = dt1 << get column names( string );

For( i = 1, i < a, i++,
	Eval(
		Substitute(
				Expr(
					If( Try( __col__ << get name, "" ) != "",
						__col__ << Set Property(
							"Spec Limits",
							{LSL( _LSL_ ), USL( _USL_ ), Target( _Target ), Show Limits( 1 )}
						)
					)
				),
			Expr( __col__ ), Parse( "dt1:Name(\!"" || Column( dt2, "ColumnName" )[i] || "\!")" ),
			Expr( _LSL_ ), dt2:LSL[i],
			Expr( _USL_ ), dt2:USL[i],
			Expr( _Target ), dt2:Target[i], 

		)

	)
);

Also, I added in a check to make sure the column is found in the target data table, so the script does not error out, and stop processing before all records are processed.

[#3]

@txnelson This really worked like a charm. However, can you help me understand what this part does? I tried using the name function but couldn't make it to work. Again, thanks a lot for your help.

( "dt1:Name(\!"" || Column( dt2, "ColumnName" )[i] || "\!")" )

[#4]

The issue is a common one when writing JSL.....you have special characters in your column names.  So, for example, your column name could be

     A+B

and what you are attempting to do, is to set a column property....in this case, Spec Limits.  So if the command is directly written out, it would look like:

:A+B << Set Property( "Spec Limits", { LSL( 22 ), USL( 55 ), show limits( 1 ) } );

JSL will not like this, because it will interpret this as a column named "A" which you are adding the value from a variable name "B", and then applying the Set Property to it.  JSL would just throw an error.

So, the solution is to use the :Name() function.

:Name("A+B") << Set Property( "Spec Limits", { LSL( 22 ), USL( 55 ), show limits( 1 ) } );

This will force JSL to interpret A+B as a column name.  Now, to make this even more precise, if we add a pointer to a data table to the front of this, it will ensure that not only is it pointing to a specific column, it is pointing to a specific column in a specific data table

dt1 = data table("..........");
dt1:Name( "A+B" ) << Set Property( "Spec Limits", { LSL( 22 ), USL( 55 ), show limits( 1 ) } );

Now to your specific question:

( "dt1:Name(\!"" || Column( dt2, "ColumnName" )[i] || "\!")" )

It really needs to be looked at in a more complete form

Parse( "dt1:Name(\!"" || Column( dt2, "ColumnName" )[i] || "\!")" ) )

We need to always examine JSL, from the inside out.  Therefore, lets say that 

i = 2;

and that the value of the second row in data table dt2 for a variable named ColumnName is A+B.

Column( dt2, "ColumnName" )[i] = "A+B"

So now the JSL code is resolved to

Parse( "dt1:Name(\!"" || "A+B" || "\!")" ) )

The next operation is simply the contatenating of 3 strings into one string.

     "dt1:Name(\!""

     "A+B"

     "\!")"

It may look like there are too many double quotes in string one and string 3.  But in JSL, \! is an escape character, indicating that the character following it is to be treated differently.  \!n is treated as a Linefeed character, and in our example, \!" is treated as an embedded double quote.  

So once the concatenation is complete, the JSL looks like

Parse( "dt1:Name(\!"A+B\!")" ) )

The final step is the resulting action of the Parse() function.  It will parse the string, and get it ready for execution.  In our case it simply means that it will remove the outer double quotes, and resolve the \!" down to a simple double quote, leaving it with

dt1:Name("A+B")

This is the exact form of what is required as the syntax for referencing a column that has special characters in its name

dt1:Name( "A+B" ) << Set Property( "Spec Limits", { LSL( 22 ), USL( 55 ), show limits( 1 ) } );

I hope this helps

[#5]

Thanks a ton for the explanation.

[#6]

@txnelson , Is it possible to add spec limits to a data table if the rows are not in the same order as columns? For example,

the table Example1 will be the same as it is, but the table Example2 will be:

How can I achieve this? Thank you for any input. 

[#7]

The script reads through the limits table, row by row.  It then gets the column name from whatever row it is currently on, and applies the limits to the data table column by Column Name, not column number.

Therefore, the order of the coumns in the data table is not important

[#8]

@txnelson, but when I run the script against the changed table, the spec limits do not get entered into the "Acidic Variants" column.

[#9]

Then I suspect there is a difference in the column name, and the name in the limits data table

[#10]

@txnelson thank you for responding so promptly.

[#11]

@txnelson I realized that it only doesn't take the spec limits from the last row of example2. I still do not know why, but I will update if I find out. 

Thanks.