- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
check if column exists?
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?
How to check if list of columns exist and report those that are missing using a pop-up window?
Determining if a column exists
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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,"");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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,"");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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 ) ) )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
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:
data table : message column exists(colname)?
delivered with JMP18:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: check if column exists?
some more insights:
- cool!
Didn't know/notice that the comparison works without the string option of get column names
as robust as @txnelson'stry(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);