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
morenrx1
Level I

Sort Columns in Descending Order and use 4 largest for calculation

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 #". 

morenrx1_1-1579140890167.png

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
txnelson
Super User

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
morenrx1
Level I

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
txnelson
Super User

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.

nozzle.PNG

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
morenrx1
Level I

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.

morenrx1_0-1579218521689.png

My overall goal would be something like this:

morenrx1_1-1579218605945.png

(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!

 

 

txnelson
Super User

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
morenrx1
Level I

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,

image.png

 

txnelson
Super User

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