Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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!

 

 

Highlighted
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
Highlighted
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

 

Highlighted
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
Article Labels

    There are no labels assigned to this post.