Choose Language Hide Translation Bar
Highlighted

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

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

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

## 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 );
theOutput = theOutput || ", " || Char( theList );
theOutput = theOutput || ", " || Char( theList );
theOutput = theOutput || ", " || Char( theList );
theOutput;``````
Jim
Highlighted

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

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

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

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