cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
markschahl
Level V

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


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.

1 ACCEPTED SOLUTION

Accepted Solutions
mpb
mpb
Level VII

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

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-ftWidth-inWeight-lb
110100
220200
330300
440400
550500
660600

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.

View solution in original post

2 REPLIES 2
mpb
mpb
Level VII

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

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-ftWidth-inWeight-lb
110100
220200
330300
440400
550500
660600

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.

markschahl
Level V

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

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.