topic Re: Comparing rows and columns with special characters in Discussions

Comparing rows and columns with special characters

Aam_jmp — Wed, 21 Mar 2018 14:24:54 GMT

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

Re: Comparing rows and columns with special characters

uday_guntupalli — Wed, 21 Mar 2018 14:49:36 GMT

@Aam_jmp,
If the % symbol is part of your column name, like let us say column name is "%x", then try using Name() function to identify the column. If you could provide a sample data set and your script, we can look at it to see whats wrong

Re: Comparing rows and columns with special characters

Aam_jmp — Wed, 21 Mar 2018 16:10:14 GMT

The names are like this: Titer (%) 1, Titer(%) 2 and so forth

Re: Comparing rows and columns with special characters

uday_guntupalli — Wed, 21 Mar 2018 16:19:28 GMT

@Aam_jmp,
Please kindly re-read my response and try using the following sample. The Name function will be able to handlt the problem you have. I am posting a sample of the same. Unless you share your script and sample data, it is hard to see what is causing the error.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); // Open Sample Data 

MeanWeight = Col Sum(dt:weight); // Calculate Column Mean 

dt << New Column("% of Total Weight",Numeric,Format("Percent",10,2),Continuous,Formula(:weight/MeanWeight)); // Define New column with % in name 

dt << New Column("Test",Numeric,Format("Percent",10,2),Formula(Col Cumulative Sum(:Name("% of Total Weight")))); // Define Test Column that demonstrates the use of Name function that can capture the column name with % in it

Hope this explains my suggestion a little better, if not kindly offer a little more detail so I can try and help

Re: Comparing rows and columns with special characters

Aam_jmp — Wed, 21 Mar 2018 18:56:12 GMT

@uday_guntupalli Even though I cannot provide the table, a part of what I want to achieve is to be able to parse column names. I have made up data tables which look somewhat similar. I want the below code to work for this data.

Names Default To Here( 1 );
dt1 = data table( "Example1" );
dt2 = data table( "Example2" );
dt4 = dt2:Column 1 << get values();
a = n rows(dt2);
For(i=1,i<=a,i++,
Parse("dt1:" || dt4[i]);
);

Re: Comparing rows and columns with special characters

uday_guntupalli — Wed, 21 Mar 2018 17:28:14 GMT

@Aam_jmp

dt1 = data table( "Example1" );

dt2 = data table( "Example2" );

ColValuesEx2 = dt2:Column 1 << Get Values; 

a = n rows(dt2);

Res= {}; 

For(i=1,i<=a,i++,
 temp = Concat("dt1:",ColValuesEx2[i]);
 Insert Into(Res,temp);
 );
 
Show(Res);

Try this. The issue I see with what you were previously doing was, ColName does not refer to an actual column. When you try to scope into data table 2, you should refer to a column that exists in the data table.

I used concatenate instead of Parse and I get the result you want .

Re: Comparing rows and columns with special characters

Aam_jmp — Wed, 21 Mar 2018 19:54:44 GMT

Why doesn't it give the output I want with using Parse even after I give the correct name of the column though? Is there a way I can parse special characters like % and ()? @uday_guntupalli

Re: Comparing rows and columns with special characters

uday_guntupalli — Wed, 21 Mar 2018 20:16:48 GMT

@Aam_jmp,
If you look at the documentation of Parse() which you may find under Help => Scripting Index ==> Parse ,
Parse by its definition is expecting a string, however you are passing 2 arguments in your implementation and expecting them to be concatenated. This is the reason I could see why it does not work even if you provide the correct name of column. Scripting Index is your friend and use it more and more to help yourself with the syntaxes and function definitions.

Re: Comparing rows and columns with special characters

Aam_jmp — Wed, 02 May 2018 15:44:21 GMT

Uday, thanks for the help. I am teaching myself this part. However, I was trying to accomplish putting in spec limits to a data table using another data table. I can do this smoothly if the names of columns do not have special characters. However, I am unable to handle the special chars in my data tables. One approach I tried was to find the (%) in my column names and replace them with a space. Even though it worked out, I do not think that it is the best solution to my problem. I know that I can use an addin to add spec limits but it would be good to know how I can solve this issue with jsl. Thanks for any input you could provide on this. My code is:

dt1 = Data Table( "Example1" );
dt2 = Data Table( "Example2" );
a = N Rows( dt2 );
For( i = 1, i < a, i++,
	Eval(
		Substitute(
				Expr(
					__col__ << Set Property( "Spec Limits", {LSL( _LSL_ ), USL( _USL_ ), Target( _Target ), Show Limits( 1 )} );

				),
			Expr( __col__ ), Parse( "dt1:" || dt2:ColumnName[i] ),
			Expr( _LSL_ ), dt2:LSL[i],
			Expr( _USL_ ), dt2:USL[i],
			Expr( _Target ), dt2:Target[i], 

		)

	)
);

Re: Comparing rows and columns with special characters

uday_guntupalli — Thu, 22 Mar 2018 14:12:07 GMT

@Aam_jmp,
Maybe this is a helpful post you can learn something from https://community.jmp.com/t5/Discussions/Spec-limits-issue/m-p/50166.

I am trying to understand reading through the thread what your actual question was ? It will probably help if you defined what your goal is - so the threads are easier to answer and will be beneficial for the community in general.

Re: Comparing rows and columns with special characters

txnelson — Thu, 22 Mar 2018 14:44:55 GMT

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.

Re: Comparing rows and columns with special characters

Aam_jmp — Thu, 22 Mar 2018 15:05:32 GMT

@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] || "\!")" )

Re: Comparing rows and columns with special characters

txnelson — Thu, 22 Mar 2018 16:29:49 GMT

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

Re: Comparing rows and columns with special characters

Aam_jmp — Thu, 22 Mar 2018 17:20:59 GMT

Thanks a ton for the explanation.

Re: Comparing rows and columns with special characters

Aam_jmp — Mon, 26 Mar 2018 19:11:36 GMT

@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.

Re: Comparing rows and columns with special characters

txnelson — Mon, 26 Mar 2018 19:40:26 GMT

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

Re: Comparing rows and columns with special characters

Aam_jmp — Mon, 26 Mar 2018 19:52:37 GMT

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

Re: Comparing rows and columns with special characters

txnelson — Mon, 26 Mar 2018 20:11:27 GMT

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

Re: Comparing rows and columns with special characters

Aam_jmp — Mon, 26 Mar 2018 20:49:58 GMT

@txnelson thank you for responding so promptly.

Re: Comparing rows and columns with special characters

Aam_jmp — Mon, 26 Mar 2018 21:00:26 GMT

@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.