5. Ranking the rows of a table

MERLIN has numerous options for ranking the rows of a distribution table, i.e. displaying them in descending or ascending order of the values they contain.

The minimum specification to achieve this is label control <G1B> (which means ‘Group level 1 Begins’) in the first item to be ranked. Usually this is the first item of the variable, causing all the rows to be ranked, but you may want some unranked rows at the start and/or the end. The latter effect is achieved by including <G1E> (‘Group level 1 Ends’) in the last item to be ranked. Here is an example:

X=’
Client brand; !unranked item at start
Rival brand 1<G1B>; !ranking group level 1 begins
Rival brand 2;
Rival brand 3;
Rival brand 4<G1E>; !ranking group level 1 ends
Other brands; !unranked items at end
Dk/na’,

Which column is to be used for ranking?

By default, rows are ranked using the values in the total column (column 0), but this can be changed with format RNC, e.g. RNC3 means the table will be ranked using the values in column 3 of the banner. If the table contains columns of statistics and you want to base the ranking on one of these, specify the relevant format in ’quotes’, e.g. RNC’MED’ will rank the table using the values in a column of medians.

RNC-1 is a special setting which means the rows will be sorted into alphabetical order of the row labels, regardless of the values.

Ascending or descending order?

By default, rows are ranked in descending order of the values, but format RVR specifies reverse ranking, meaning the rows will appear in ascending order.

In the case of alphabetical sorting, the order is always A-Z.

Numbers or column percentages?

Ranking may be based on the raw numbers, or on column percentages (regardless of which data types are shown). Usually, of course, this produces the same result (and, by default, raw numbers are used) – but if label control <B> is used to change the base for percentaging, you may choose to rank the rows according to the percentage values, by setting format RNV2. You would also, in this example, need to link the base row to the row it applies to, so they are always kept together – which brings us to the next topic…

Linking rows together

<G1N> means this Group level 1 item is linked to the Next item, so should always be moved with it, regardless of the value it contains. This is what we need for the example mentioned above:

X=’
Base for brand 1<B><G1N>; !this item is linked to the next one
Brand 1<G1B>; !ranking group level 1 begins
Base for brand 2<B><G1N>;
Brand 2;
Base for brand 3<B><G1N>;
Brand 3<G1E>; !ranking group level 1 ends
Base for other brands<B>; !unranked items at end
Other brands’,

<G1P> applies the same principle to the Previous item, as in this example where we are ranking three header groups according to the ‘yes’ response in each group:

X=’
Brand 1<g1n>\Yes<G1B>; !ranking group level 1 begins
No<G1P>; !this item is linked to the previous one
Dk<G1P>;  !this item is linked to the previous one
Brand 2<g1n>\Yes;
No<G1P>;
Dk<G1P>;
Brand 3<g1n>\Yes<G1E>; !ranking group level 1 ends
No<G1P>;
Dk<G1P>’,

Other types of ranking

You may define multi-level (nested) ranking by using different group level numbers (up to 9 levels are allowed). This can become very complex and we won’t cover it here, but see example 8.1 in the Tips and Examples library. The most common examples of multi-level ranking can be done using the NET command in a variable definition, which handles netting and ranking in a single operation.

You may rank statistics that are in consecutive continuation tables using format RCG. See example 9.5 in the Tips and Examples library.

Disabling ranking

Format RNK is true by default but if set false, will disable all ranking, whether done with label control <G> or NET. This provides an easy way to switch ranking on and off without changing your variable definitions.

Any questions? Email support@merlinco.co.uk.