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

- JMP User Community
- :
- Discussions
- :
- Sort Columns in Descending Order and use 4 largest for calculation

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

Highlighted
##

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

Sort Columns in Descending Order and use 4 largest for calculation

Jan 15, 2020 6:27 PM
(734 views)

I have a data table and I need to get the first 4 largest values from a range of columns, labeled "Nozzle #". These column values vary by variable, or column labeled "Test #".

I then need to apply these values and in a new column calculate area of a circle per test #. Since I have a larger data table, I would like to either group the columns using the For, If, and Contains logic or is there a more efficient approach in JSL?

7 REPLIES 7

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

Do you want the first 4 largest values across the Nozzle # columns for each row, or the first 4 largest values for all of the rows for all of the Nozzle # columns?

Jim

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

I would like the first 4 largest Nozzle values per test. So, it would be:

18684 = 5.5 5 4 0

18685 = 5 5 4 0.5

18688 = 5.5 5 4 0.5

18689 = 5 5 0.5 0

18684 = 5.5 5 4 0

18685 = 5 5 4 0.5

18688 = 5.5 5 4 0.5

18689 = 5 5 0.5 0

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

Here is an example of one way to do this. I admit it is a little crazy, but it should show you some JCL that will give you an idea of what you can do. I have done the calculation in a new column in the data table you displayed in your first inquiry.

Here is the formula I developed for the new column

```
theList = {};
Insert Into( theList, :Nozzle 1 );
Insert Into( theList, :Nozzle 2 );
Insert Into( theList, :Nozzle 3 );
Insert Into( theList, :Nozzle 4 );
Insert Into( theList, :Nozzle 5 );
Insert Into( theList, :Nozzle 6 );
theList = Sort Descending( theList );
theOutput = Char( theList[1] );
theOutput = theOutput || ", " || Char( theList[2] );
theOutput = theOutput || ", " || Char( theList[3] );
theOutput = theOutput || ", " || Char( theList[4] );
theOutput;
```

Jim

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

So I tried putting the columns into a list using a for loop, shown as *List1st* in the nozzle_script.jsl file. Whenever I try sorting them, only the column names are sorted and not the column values. I also tried using the code you have shown and the lists are shown as empty in the Log, shown below.

My overall goal would be something like this:

(note: I manually entered the "Largest" column values. Then applied ("Largest"/4)^2 formula for the "Area" columns.)

I have attached the data table and my script below. Thank you!

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

Try using this form

```
dt = Current Data Table();
theList = {};
For( i = 2, i <= 7, i++,
Eval( Parse( "Insert Into( theList," || Char( Column( dt, i )[Row()] ) || ")" ) )
);
theList = Sort Descending( theList );
```

Jim

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

Tried it out, code shown below, but it gives me all the values sorted.

```
theList = {};
For( i = 2, i <= 7, i++,
For Each Row(
Eval( Parse( "Insert Into( theList," || Char( Column( dt, i )[Row()] ) || ")" ) )
)
);
theList = Sort Descending( theList );
```

Yielding,

Highlighted
##

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

Re: Sort Columns in Descending Order and use 4 largest for calculation

Please take a look at the first formula I gave you. It first creates a list of all of the values for the different columns for the given row. It then sorts the values and finally it takes the first four values into a character string.

If you add that code to the latest code, it will result in a value for the column that contains the first 4 largest values.

Jim

Article Labels

There are no labels assigned to this post.