QlikChallenge #1: Getting the max value over multiple expressions

And you want to calculate the highest expression of Value_1, Value_2, etc. within your load script to get the following result:

How would you solve this?
Just use the comment section to post your ideas … I am looking forward to reviewing your solutions …

Regards
Stefan

P.S.: Ralph & Team: You are not allowed to participate in this competition because you have already received my solution for that question

Possibly related posts:

qlikblog.at is now published in English! About four months ago I have created this blog for sharing my knowledge and ideas about business intelligence and especially QlikView. In the last weeks I have received so many mails! Thanks! But most of...

15 Comments

Hi Stefan,
i had the same (but a little more complicated) requirement these days. My idea is in the script to get the values in separate table using CrossTable. Then calculate the max value per “case” (in another temp table), order this values and set RowNo() as sorting field. In final there is original table and sort table linked to original by “case” field.
I’ve made a simple qvw file that works with data that you provide http://dl.dropbox.com/u/63800/sorting.qvw
Hope that helps

I think the preceeding load would normally be faster to execute, and it’s certainly less script. However, I’d like to see a more general solution. Things to consider:

– We shouldn’t rely on the pattern in the field names. I don’t think anyone has, and a couple people have basically stated this explicitly. I agree with them.

– In some cases, we might want a field name instead of a number.

– What should we do for cases where more than one field has the maximum value? Just take the first? If so, first in what sequence? Return all of them in a concatenated value?

– What if there are other fields in the table besides the key and the values? Text fields, for instance, or a number that shouldn’t participate in the maximum? We would want to ignore those extra fields. I think this should be handled by specifying the list of fields we care about. And we should only have to give that list ONCE to avoid future maintenance problems as we add and remove fields from the list.

So here are a couple of solutions. Both specify the fields to include in an inline load, with the field number assigned by the position in the list. The first solution uses this field number to return only the FIRST maximum field and field number. It’s pretty much Rob’s solution with the mentioned additions:

The second solution returns a list of maximum fields and field numbers. It uses the field number to put the list of maximum field names in the desired order. I’m… not really satisfied with this one. Way too many joins for my taste. I’m betting it could be significantly simplified, but I really should do something else with my evening.