241461_en-US

liliconcom

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? 

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.

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,

<SPAN class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 814px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="image.png" style="width: 814px;"><span class="lia-inline-image-display-wrapper" image-alt="image.png" style="width: 814px;"><img src="https://us.v-cdn.net/6038925/uploads/images/21024iC715F1791A69F0B3/image.png" role="button" title="image.png" alt="image.png" /></span></SPAN></SPAN>

 

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

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!

 

 

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;

Re: Sort Columns in Descending Order and use 4 largest for calculationI 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 calculationDo 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?