Trying to use DUAL for ratings ranking.

[#1]

I have a series of clients and and they are rated as big/med/small.

Some clients are in the data multiple times with different ratings. For example, Company A can be rated 'big' in one entry, and 'small' in another entry.

In the case of multiple ratings, I want the field to return the highest rating so Company B which is listed as both 'med' and 'small' will return only 'med'.

To accomplish this, I'm thinking of using the dual function to convert 'big', 'med', 'small' into numbers (i.e. 1,2,3) so I can use an "if then max" function.

Can someone show me the syntax in order to accomplish this or if there is a better way doing this?

Thanks in advance!

[martin59]

I'm not sure but you can try with aggr() function :

max(aggr(Count(rows), rate))

This function returns the number of rates for the most rate represented

[martin59]

if you want the rate label with max values, you can try this kind of thing :

subfield(concat(aggr(rank(count(<Rate> rows)) & '#' & Rate & '#',Rate)),'#',2)

[#4]

Another approach would be to determine the rating for each client in your load script. With this approach if you made a selection that did not include the max rating for the client, you can still display the max rating.

1) load the table as you do now (I'll call it table1 in this example)

2) load a new table that contains client and their max rating

table2:
load
 client,
 max(rating) as max_rating
resident table1
group by client;

3) join the max rating just created back to your original table

join (table1)
 client,
 max_rating
resident table2;

[martin59]

The problem with your solution is that you are no longer dependent on your selections on other fields

[#6]

If you do not want to make changes to your script, you could use the text itself for the rating. Fortunately in your case, the first letter in the ratings are in sequential order. You could use something like

=if(Chr(Min(ord(Rating)))='B','Big',if(Chr(Min(ord(Rating)))='M','Medium','Small'))

I have attached a small sample demonstrating this.

Let me know if this is what you were looking for.

Nimish

[johnw]

Untested, but use dual() to give rating a numeric value in the script:

dual(rating,match(rating,'small','med','big')) as rating

Then to get the max rating back as text for your current selections and dimension values:

pick(max(rating),'small','med','big') as rating

Though Nimish is right that for now, you could use the fact that your ratings have a text sequence, but it's even simpler than the expression he gave:

minstring(Rating)

[#8]

Thanks John for the simplification. Did not know about minstring.

Nimish