cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mwechtal
Level III

Determining if a column exists

I have a JMP script that creates a new column with a formula in it. I'm trying to find out how to detect if the column already exists so that I can skip creating it, and avoid generating extra columns. I'm sure it's there, but I'm not finding it in the literature that I have.

Thanks.

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Determining if a column exists

 

dt = open("$sample_data\Big Class.jmp");
 
col_name_list = dt << get column names(string);
 
new_column = "BMI";
 
// English BMI Formula
// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703
 
if (!contains(col_name_list, new_column),
      dt <<New Column("BMI", numeric, continuous,
            formula(703 * :weight / :height / :height)
      );
);

 

View solution in original post

10 REPLIES 10
pmroz
Super User

Re: Determining if a column exists

 

dt = open("$sample_data\Big Class.jmp");
 
col_name_list = dt << get column names(string);
 
new_column = "BMI";
 
// English BMI Formula
// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703
 
if (!contains(col_name_list, new_column),
      dt <<New Column("BMI", numeric, continuous,
            formula(703 * :weight / :height / :height)
      );
);

 

mwechtal
Level III

Re: Determining if a column exists

Thanks, that works just fine for me. I was expecting to find a function to check for existance, but this works.

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Determining if a column exists

There is no single function that I am aware of. But Is missing() in combination with Is Scriptable() can do the trick.

The column also need to be enclosed by a Try() statement to avoid the script from stopping if the column would not exist.

 

dt = Open( "$sample_data\Big Class.jmp" );
 
new_column = "BMI";
 
If( Is Missing( Is Scriptable( Try( Column( new_column ) ) ) ),
	dt << New Column( new_column, numeric, continuous, formula( 703 * :weight / :height / :height ) )
);

 

mwechtal
Level III

Re: Determining if a column exists

Also handy to know. I'm always glad to have another tool to add to the toolbox.

Thanks.

Rob
Rob
Level II

Re: Determining if a column exists

Thanks ms!
Only your example worked for me, because the other solution is case sensitive. I had to use yours, since I could not be sure that the case of my string characters are correct.
pmroz
Super User

Re: Determining if a column exists

Here's a version of my logic that's case-insensitive.

dt = open("$sample_data\Big Class.jmp");
col_name_list = dt << get column names(string);
// Convert all column names to uppercase for case-insensitive search
for (i = 1, i <= nitems(col_name_list), i++,
	col_name_list[i] = uppercase(col_name_list[i]);
);
new_column = "BMI";
// English BMI Formula BMI = ( Weight in Pounds / ( Height in inches^2 ) ) x 703
if (!contains(col_name_list, uppercase(new_column)),
    dt <<New Column("BMI", numeric, continuous,
            formula(703 * :weight / :height / :height)
    );
);
wiebepo
Level III

Re: Determining if a column exists

I am not certain if this is true in all recent versions of JMP. I make this comment only to highlight an opportunity to improve the robustness of the code.

 

'Contains' returns the position of the item, which is fine to use in an 'if' statement, since zero will be interpreted as false and a positive value will be interpreted true. When preceding a contains with a not '!', the condition works when the item is not present or when present as the first item; however, when the item isn't first ( a value greater than 1 ), the not '!' can fail to convert the condition to false. This can be avoided by using a comparison '>0' with the 'contains'. See below for syntax.

dt = Open( "$sample_data\Big Class.jmp" );
 
col_name_list = dt << get column names( string );
 
new_column = "BMI";
 
// English BMI Formula
// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703
 
If( !(Contains( col_name_list, new_column ) > 0),
	dt << New Column( "BMI", numeric, continuous, formula( 703 * :weight / :height / :height ) )
);

 

mwechtal
Level III

Re: Determining if a column exists

That's very handy to know. I'll have to watch out for that.

Thanks

pmroz
Super User

Re: Determining if a column exists

Hello wiebepo,

 

I tried different combinations and negating a positive integer, no matter how large, results in a 0.  So my code will work (in JMP 9 and 10).  Having said that I appreciate the heads up - this approach might not work in all languages.  It certainly bears further testing.

 

b = 2000000000000;
!b;

The log shows:

0

 

Regards,

PMroz