turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Find Min and Max from multiple columns ( Same name...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 3:41 AM
(1511 views)

Hello,

I have huge data in excel and it has multiple columes with same name ( this is format i get from tester). And i would like to calculate min, max, stdev and mean from all columes.

I tried to open data in JMP but JMP remanes the colume name to ( Av, Av1, Av2, Av3.......) and then if i use tabulate it shows min, max for Av, Av1, Av2.... etc., but i wanted min and max for all ( Av, Av1, Av3, ...).

Pls help. Attached raw data for.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 20, 2017 8:41 AM
(1742 views)

Solution

All of what I am going to show you below is covered in the Using JMP book

Help==>Books==>Using JMP

What needs to be done is to create a new column in your data table, and apply a formula to the column.

1. Open the stacked data table and click on the red triangle in the column area above the row state column. Select "New Column"

2. The Column Info window will open. Change the name of the new column to whatever you want(I chose "Label 2"). Also change the Modeling Type to "Character"

3. Click on OK and then Right Click on the header area of the new column and select "Formula"

4. In the formula editor, double click on the box titled "No Formula"

5. Rather than doing the point and click formula building that we could do, with this simple formula, we just need to type it into the box

6. Once the typing is done, just click on OK or Apply and the formula will generate the new values

You can now use this new column in your Tabulate Platform

Jim

11 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 4:20 AM
(1507 views)

Try using 'Tables > Stack' before doing the tabulation.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 5:54 AM
(1496 views)

I didn't get fully.

What i tried 'table > stack" but still see A1, A2, A3.... etc.. Hence tabulate still not doing over all min and max.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 6:21 AM
(1494 views)

You will have to take more care in reading the XLSX, but if you put it on the Desktop, the script below gives the idea.

```
NamesDefaultToHere(1);
// Put the .xlsx on the Desktop and open it
dt = Open("$DESKTOP//Char Summary_web.xlsx");
// Just use the first few columns as a demonstration
dt << deleteColumns(17::NCol(dt));
// Stack the data
dt2 = dt << Stack(
columns(
:ZA 2,
:ZA 3,
:ZA 4,
:ZA 5,
:ZA 6,
:ZA 7,
:ZA 8,
:ZA 9,
:ZA 10,
:ZA 11,
:ZA 12,
:ZA 13,
:ZA 14
),
Source Label Column( "Label" ),
Stacked Data Column( "Data" ),
OutputTable((dt << getName)||" Stacked")
);
// Use Tabulate
dt2 << Tabulate(
Show Control Panel( 0 ),
Add Table(
Column Table( Analysis Columns( :Data ), Statistics( Mean, Std Dev ) ),
Row Table( Grouping Columns( :wafer ) )
)
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 7:00 AM
(1489 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 7:10 AM
(1483 views)

The JMP platforms in JMP are very functional, and that's true for 'Tables > Stck' too. The documentation is generally good, and if not, you can suggest how it could be improved.

Take a look at the Multiple Series Stack option.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 10:13 AM
(1476 views)

You didn't get me. My problem label still has A, A10, A11, A12, ... I want min and max from all Av.. A10....

Pls help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 10:57 AM
(1472 views)

Just don't drag the column called Label to the Tabulate Platform. Just drag the column called Data. Dragging the Label column is forcing the agragation.

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 12:08 PM
(1465 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 19, 2017 12:30 PM
(1463 views)

Is this what you are looking for?

If it is, all I did was to add a new column to your stacked data table, calling it "New Label" and specifying a formula for the column as:

Word(1, :Label, " ")

The interpretation of the formula is to look at the "Label" column, and take the first Word from that column, where the delimiter is based upon blanks, " "

Jim