topic check if column exists? in Discussions

check if column exists?

hogi — Wed, 28 Jun 2023 16:32:59 GMT

Hi,

I want to create a new column and before doing so:

ask the data table if the column already exists.

Is there a "best" way such that the code can be shared with other colleagues in the company?

Re: check if column exists?

Jed_Campbell — Wed, 23 Nov 2022 16:05:45 GMT

Seems like each of those solutions would work. The most robust would likely be the 2nd link, but it also has the most lines of code.

Re: check if column exists?

hogi — Wed, 23 Nov 2022 16:23:45 GMT

my farorite: Jim's idea to just ask the data table for the column and check if the data table complains.

But even this approach relies on the fact that the spelling of the column name while asking fits to the actual name.

e.g. it doesn't find "a g e" if you ask for "age".

But the new column :age would cut all links between :age and the existing column :a g e

This could lead to such collateral damages:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt:age << set name("a g e");

Show(dt: age [5]); // points to the existing column
	
Try(:is there a = column(dt,"age"), "answer: no");

new Column("age");
Show(dt: age[5]) // points to the new column

Re: check if column exists?

txnelson — Wed, 23 Nov 2022 16:44:58 GMT

The most reliable method that I have found, is to ask for the name of the variable, << get name

It honors blanks, etc.

names default to here(1);
dt=current data table();
dt:age << set name("a g e");
tn="age";
r=try(column(dt,tn)<<get name,"");

Re: check if column exists?

hogi — Tue, 17 Jan 2023 12:49:01 GMT

Actually, no need to ask for the name, right?
The trick lies in the

Try(column(dt,"age"), check for failure);

Try to get the column - and check if it fails.

Not only ultra-short - but also an excellent choice regarding robustness :)

And - as the icing on the cake - ask the column for it's name:

r=try(column(dt,tn)<<get name,"");

A nice trick to get a well-defined return type for existing and non-existing columns. Thanks @txnelson :)

Re: check if column exists?

ErraticAttack — Wed, 23 Nov 2022 20:43:37 GMT

I believe that you're just supposed to use the As Name() function for this. It makes these checks easy and you don't have to worry about spacing / capitalizations:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt:age << set name("a g e");

Show( Contains( dt << Get Column Names, As Name( "a ge" ) ) );

Show( Contains( dt << Get Column Names, As Name( "S   eX" ) ) );

name to check = "N a ME";
Show( Contains( dt << Get Column Names, As Name( name to check ) ) )

Re: check if column exists?

hogi — Sat, 29 Jun 2024 14:58:31 GMT

Ah nice :)

To prevent users in general from forgetting the

As Name

in their code, how about adding a function to the data table object to ask if the column exists:

delivered with JMP18:

Re: check if column exists?

hogi — Thu, 24 Nov 2022 11:46:08 GMT

some more insights:

cool!
Didn't know/notice that the comparison works without the string option of get column names
as robust as @txnelson's try(column(dt,"Age"))age ~ Age

just use string as an argument for Get Column Names if you want to differentiate between age and Age

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");


Show(dt << Get Column Names(String));
Show( Contains( dt << Get Column Names(String), As Name( "Age") ) ); // -> nope

Show(dt << Get Column Names);
Show( Contains( dt << Get Column Names, As Name( "Age") ) ); // found !
Show(r=try(column(dt,"Age")<<get name,"")); // found :)

Show(dt:Age[1]);
dt << New Column("Age", Numeric, Ordinal);
dt:Age[1] = 0;
Show(dt:Age[1]);


wait(1);