topic Re: column with formula in a post query script in Discussions

column with formula in a post query script

Françoise — Mon, 05 Nov 2018 19:01:39 GMT

hi,

With a JMP query builder, and with the post query script, I create a new column with something like this:

dt << NewColumn("BMI", Numeric, Continuous, Formula(:weight/:height^2));

but, my problem is: when I update from database the jmp file, I create a new column "BMI 2".

and with an another update: a new column BMI 3.

I would like to have only one column BMI at each update from database.

is it possible?

best regards

Re: column with formula in a post query script

txnelson — Mon, 05 Nov 2018 15:33:59 GMT

Without more specifics, it is tough to say precisly what the solution is, but my initial thought is that you need to check to see if the column you are creating already exists, and if it does, then you either need to delete the existing column and then create the new column, or just skip the creation of the new column. I am not sure which of these is correct, not knowing the logic and flow of your script.

If( try( column(dt, "the column name" ) << get name, "" ) != "",
     dt << delete columns( "the column name" );
);

Re: column with formula in a post query script

cwillden — Mon, 05 Nov 2018 15:41:38 GMT

If I understand this correctly, you should be able to avoid creating new columns by checking first if the column exists before executing that line of code. Here's a way to do it:

if(Contains(dt << Get Column Names(string),"BMI")==0,
	dt << NewColumn("BMI", Numeric, Continuous, Formula(:weight/:height^2));
);