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

Sort Columns in Descending Order and use 4 largest for calculation

morenrx1 — Thu, 16 Jan 2020 02:27:17 GMT

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?

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

txnelson — Thu, 16 Jan 2020 04:01:30 GMT

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?

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

morenrx1 — Thu, 16 Jan 2020 04:18:41 GMT

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

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

txnelson — Thu, 16 Jan 2020 05:38:18 GMT

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;

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

morenrx1 — Thu, 16 Jan 2020 23:53:54 GMT

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!

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

txnelson — Fri, 17 Jan 2020 00:35:51 GMT

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 );

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

morenrx1 — Fri, 17 Jan 2020 02:44:47 GMT

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,

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

txnelson — Fri, 17 Jan 2020 05:16:56 GMT

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.