topic Re: How to easily import/assign units property to multiple columns? in Discussions

How to easily import/assign units property to multiple columns?

markschahl — Thu, 27 Feb 2014 16:23:44 GMT

I work in the petrochemical industry and like others in the process industries, I use JMP to analyze continuous/batch process measurements time-series data. Column 1 is a timestamp, columns 2 though n are process measurements like; flow; pressure; temperature; rpm; mass, etc.. The data are queried from a process historian database into Excel. The first row contains the measurement name and the second row contains the measurement units: kg/hr; deg C; kPa; psig; kbbl/hr; etc.. Is there a way have the units information assigned to the Units column property? Would be a great feature in the Excel import wizard.

Re: How to easily import/assign units property to multiple columns?

mpb — Mon, 03 Mar 2014 19:58:38 GMT

In JMP 11 the excellent Excel import wizard lets you specify how many rows are to be considered headers and where they are, and then lets you specify where the data is. Doing this interactively or using the script that gets embedded in the resulting JMP file, you can take an Excel sheet that looks like this:

Length Width Weight

ft in lb

1 10 100

2 20 200

3 30 300

4 40 400

5 50 500

6 60 600

and end up with a JMP table that looks like this:

Length-ft	Width-in	Weight-lb
1	10	100
2	20	200
3	30	300
4	40	400
5	50	500
6	60	600

If that is adequate then you are done. If not then you can run a script something like the following:

dt = Current Data Table();

For( colnum = 1, colnum <= 3, colnum++,

col = Column Name( colnum );

charcol = Char( Column Name( colnum ) );

colname = Word( 1, charcol, "-" );

colunit = Word( 2, charcol, "-" );

newcolname = col << Set Name( colname );

newcolname << Set Property( "Units", Eval( colunit ) );

);

to end up with a JMP table that looks like this:

Length Width Weight

(ft) (in) (lb)

1 10 100

2 20 200

3 30 300

4 40 400

5 50 500

6 60 600

where the text between parentheses are entered into the Units property of each column.

Re: How to easily import/assign units property to multiple columns?

markschahl — Tue, 04 Mar 2014 01:23:45 GMT

mpb:

Thanks. That works. I'm new to scripting and still shaky when it comes to using eval.

I also posed this question to my regional JMP support person Jerry Cooper. His solution:

Names Default To Here( 1 );
dt = Current Data Table();

mycols = dt << Get Column Names( string );

For( i = 1, i <= N Items( mycols ), i++,
If( Contains( mycols, "-" ),
myunits = Substr( mycols, Contains( mycols, "-", -1 ) + 1 );
newname = Substr( mycols, 1, Contains( mycols, "-", -1 ) - 1 );
Column( i ) << Set Name( newname );
Column( i ) << Set Property( "Units", eval(myunits) );
)
);

He contacted the developers to add this feature to the Excel Import Wizard. Another situation requiring something similar is specification limits. How to import them with the data and assign them to the column properties.