795698_en-US

liliconcom

Try in context of Transposition

I have a script that performs a transposition of some data obtained from a SQL query.  I would like to add some forgiveness to the script whereby the exact columns in the query can vary.  My current script mandates a series of columns that need to be present for it to work and I'd like to apply the Try() functionality somehow to accommodate variance in the SQL query.

 

 I did an exercise with the transpose included in a try statement but that failed.

Automation and ScriptingRe: Try in context of Transposition

Which part do you have issues with? SQL query failing because column names change? Or JSL script failing because it doesn't get the columns it expects? What should happen in case of an issue?

Re: Try in context of Transposition

SQL pulls the data as expected but the operator might not include all the columns JMP expects to transpose.  The problem then occurs at the JMP end where explicit columns are nominated but turned up missing.

 

The JMP log indicates

 

Column not found in access or evaluation of 'columns' , Bad Argument( {:SAMPLE} ).

 

Re: Try in context of Transposition

And what should be done in that case? Stop the program execution? Skip that part of the script? Attempt to get the correct columns?

Re: Try in context of Transposition

I only want to execute on the columns that actually turned up and not fail because of the missing ones.  I ultimately need a script that has some forgiveness for differences in the SQL query, as many operators need to use it and differences happen.  One advantage I have is that the Transpose Column (:VALUE) and the Label column (:PARAMETER) have fixed titles, I dug into this a bit with some help from the information I found here and elsewhere, I got the script below which seems to work.

 

Solved: remove elements in a list - JMP User Community

 

Thanks for the help.

 

dt = Current Data Table();

// List available columns

by_col_names = dt << get column names( "String" );
show(by_col_names);

// Remove Transpose and Label columns from list

delcols = {"VALUE", "PARAMETER"};

For( i = 1, i <= N Items( delcols ), i++,
	Remove From( by_col_names, As List( Loc(by_col_names, delcols[i] ) ) );
);

// Transpose only if valid columns exist

If (N Items(by_col_names) > 0,
    dt2 = dt << Transpose(
        Columns(:VALUE),
	By(
		by_col_names 
	),
	Label( :PARAMETER ),
        Output Table( "Transpose of dt_data" )
    );
);