Subscribe Bookmark RSS Feed

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

markschahl

Community Trekker

Joined:

Jun 18, 2012


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
Solution

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.

2 REPLIES
Solution

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

Community Trekker

Joined:

Jun 18, 2012

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.