How to check if list of columns exist and report those that are missing using a pop-up window?

[#1]

Hi all!

I would like to test if a list of 5 column names exist in a data table. 

If one or more are missing, I would like to have a window pop up telling the user which columns are missing. If all columns are present the script should continue its run.

Could you please point me in the right direction on how to write it?

Thanks!

[#2]

Hi Lavik17,

Have you tried using the Columns View Selector in the column drop-down menu? Then, using the summary statistics function, you can effortlessly search your five-column names and determine if they are missing data. 

I hope this helps, 

Rob

[#3]

 Hi Rob! 

Thank you for the suggestion! I would like to conduct this task using JSL because I am planning to incorporate it in some of my scripts, but would also like to have a user friendly GUI message for the users in case columns are missing.

Thanks though!

Adi

[#4 · Accepted solution]

Here is a simple example of one way to do this

Names Default To Here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colList = dt << get column names( string );

requiredList = {"name", "sex", "shoe size"};

For( i = N Items( requiredList ), i >= 1, i--,
	If( Contains( colList, requiredList[i] ),
		Remove From( requiredList, i, 1 )
	)
);

stop = "NO";
If( N Items( requiredList ) > 0,
	stop = "YES";
	New Window( "Error",
		Modal,
		V List Box(
			Text Box( "The following column(s) are required" ),
			Text Box( "but were not found in the data table." ),
			Spacer Box( size( 1, 10 ) ),
			List Box( requiredList )
		)
	);
);
If( stop == "YES", Throw() );

[#5]

Thank you! This is exactly what I was looking for!

[#6]

You can use the Remove() method of Associative Arrays to do this without any JSL loops:

Names Default to Here( 1 );

reference table = New Table( "TEST",
	<<New Column( "A" ),
	<<New Column( "B" ),
//	<<New Column( "C" ),
	<<New Column( "D" ),
//	<<New Column( "E" ),
	<<New Column( "F" ),
//	<<New Column( "G" ),
	<<New Column( "H" ),
	<<New Column( "I" ),
	<<New Column( "J" ),
	<<New Column( "K" ),
	<<New Column( "L" ),
	<<New Column( "M" ),
	<<New Column( "N" ),
	<<New Column( "O" )
);

cols of interest = {"B","C","D","E","F"};

table cols = reference table << Get Column Names( "String" );

table cols aa = Associative Array( table cols );
cols of interest aa = Associative Array( cols of interest );

cols of interest aa << Remove( table cols aa );

cols missing = cols of interest aa << Get Keys;

If( N Items( cols missing ),
	New Window( "Error",
		<<Modal,
		Text Box( "Some columns are missing!" ),
		Text Box( Concat Items( cols missing, ", " ) )
	);
	Throw()
);

Print( "rest of script..." )

[#7]

Thank you! This is an interesting solution! I really should learn to work with Associative Arrays...

[#8]

The approach with the associative array is great!

But please note that the comparisons described here just find exact matches.

In general for Jmp it doesn't matter if there are capital letters in the column name - or spaces.

So, depending on your application case, maybe the check if list of columns exist shouldn't care either.

Very robust is @txnelson 's approach :

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

check if column exists? 

With all the pitfalls of column existence checks - in my opinion Jmp should provide a robust way to ask a data table if a column exists.
Here is the post in the wish list:
data table : message column exists(colname)?