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
- :
- Problem of extracting data from excel

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

Apr 27, 2017 6:38 PM
(1703 views)

Hi, everyone

Is it JSL have possible to extract the data from excel file which can make like S_GX_F40(Family_D) have these two parameters list(G700 and S800-see second attachment[i use the excel to make a small summarize] )??What the problem that i meet is when i run the script to extract the data from the excel file, it can extract out the names and parameter list out, BUT it extract all the rows of the excels file.

But then what i now want is like photo 2: One Family_D generate 2 parameter list(meant now i have 5 Family_D then each of Family_D has 2 parameter list). So, in my final output, I only want to extract 5 Family_D and 2 parameter list in order to generate the chart.(That meant i only have 5 Family_D x 2 parameter list But NOT 10 Family_D(every row of excel) x 2 parameter list).Therefore, my final is only want 5 interface(5 Family_D) but inside have 2 parameter graph each.

So, can anyone help me solve this out?

thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Apr 28, 2017 8:29 AM
(3187 views)

Solution

This seems like homework...

If I understand you correctly you want to re-create the pivot table in JMP. After importing the table you might start with the Tabulate platform and do something like this:

```
dt = New Table( "Untitled",
Add Rows( 4 ),
New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);
dt << Tabulate( Add Table( Row Table( Grouping Columns( :a, :b ) ) ) );
```

Giving output similar to this:

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

May 3, 2017 3:42 AM
(3083 views)

Solution

Building on the previous reply:

```
// Make an example table
dt = New Table( "Untitled",
Add Rows( 4 ),
New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);
// Make a list of columns, 'gCols', to be used for grouping (in this case, it's all the columns in the table)
gCols = dt << getColumnNames("String");
// Do the tabulation using the list
dt << Tabulate( ShowControlPanel(0), Add Table( Row Table( Grouping Columns( Eval(gCols) ) ) ) );
```

4 REPLIES

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

Apr 28, 2017 8:29 AM
(3188 views)

This seems like homework...

If I understand you correctly you want to re-create the pivot table in JMP. After importing the table you might start with the Tabulate platform and do something like this:

```
dt = New Table( "Untitled",
Add Rows( 4 ),
New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);
dt << Tabulate( Add Table( Row Table( Grouping Columns( :a, :b ) ) ) );
```

Giving output similar to this:

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

May 2, 2017 6:53 PM
(1602 views)

May I know is it possible to do the pivot table without hard coding? Because I have a lot of data, it is impossible for me to list down what is the value of a, b, c, d, e etc..

So, is there any alternatives way can do it?

Thanks.

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

May 3, 2017 3:42 AM
(3084 views)

Building on the previous reply:

```
// Make an example table
dt = New Table( "Untitled",
Add Rows( 4 ),
New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);
// Make a list of columns, 'gCols', to be used for grouping (in this case, it's all the columns in the table)
gCols = dt << getColumnNames("String");
// Do the tabulation using the list
dt << Tabulate( ShowControlPanel(0), Add Table( Row Table( Grouping Columns( Eval(gCols) ) ) ) );
```

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

May 3, 2017 5:58 AM
(1568 views)