Select best fit

Question

Hello,

I am having trouble selecting specific values from my data. The problem looks like this:

There are 4 columns containing names (always a batch), years (differing within each batch), seasons (sometimes the same season for batch and year) and a prioritization for those seasons according to the month the data has been sampled in (Priorities 1 to max. 4). 1 represents the best match in all cases but especially if the same season per year and batch of names comes up more than ones. It is easy to select all 1st priorities, however, in this case I would lose data in case some of the double seasons do not contain a 1st priority but a 2,3 or 4.

Can somebody come up with a script that helps me to select all 1st values and if those are not available the second best option available? It could also be the case that only priorities 3 and 4 are available and therefore the 3rd best option must be picked (which in this case would actually be the 1st best option as no 1 or 2 are available).

I´ll attach an example file.

Thanks a lot for your help!

example data.jmp

Mark_Bailey · Answer

The Col Rank() function with the arguments that you provided is a nested sort.

Pacco · Answer

Hi Mark,

Thank ypou for the answer. After a short google on what a nested sort is I´m not much more enlightened.

I figured out another way now and hope it worked for all data rows:

dt = Current Data Table();
dt << New Column( "rank", Formula( Col Rank( (:season priority), :name, :year, :season, :surf_bottom  )) );

If you have time to shortly explain to me what a nested sort would do in this case I would highly appreciate it to apply this in another case.

I already tried to sort it and select the values by using an if statement and lag functions but it never resulted in containing all rows I needed.

/Astrid

Mark_Bailey · Answer

Wouldn't a nested sort of this data table provide the information that you want?