topic Find min and max value in a column in QlikView

Find min and max value in a column

Thu, 25 Feb 2010 16:38:13 GMT

Hello.

I have a table with a column (let's say A) with many values. I need to give a full score of 10 to the row where its value in column A is the biggest and a score of 0 to the minimum value of column A. How can I state which are those values please? I've tried with max() and rangemax() functions, but with no success.
If this can help, consider that value in column A are not ready when data are imported into QlikView, but they are calculated in the table itself.

Thank you,
Luca

Find min and max value in a column

Thu, 25 Feb 2010 16:43:22 GMT

Hi Luca, please see below if it's what you're looking for.

-Lukas

Find min and max value in a column

Thu, 25 Feb 2010 16:56:56 GMT

Hi Lukas.

Thank you for your help. Unfortunately that's not exactly what I'm looking for. Please have a look to the attached file: your example works because F1 are value stored as they are in the QV data source, but in my case they are obtained by an expression. I can't have the max value in this case.

There is a table in my file with an example.

Find min and max value in a column

Thu, 25 Feb 2010 17:10:10 GMT

Hi Luca, it makes things more complex, but should be doable in most cases.

I've updated the example with a column that determines where F1+F2 has the biggest / smallest value.

Might help if you could post your qvw.

-Lukas

Find min and max value in a column

Thu, 25 Feb 2010 17:29:27 GMT

I can't post the original document, but I've made a new one, with the same problem. I have some customers with the transact log of all their purchases. For each transaction I have buy and sell price, so I know the markup. In the table I need to populare "Max markup" column with 15,60 €, the highest markup of the whole table.

If I can calculate this, I will calculate also the min markup and I'll be able to give the scores 0-10 as needed.

MS Excel has natively this function: MAX(<range>).

Thanks for your help and time!

Find min and max value in a column

Fri, 26 Feb 2010 08:34:35 GMT

Hi Luca,

hope below helps. Basically how it works is that QV aggregates the data first based on dimension customer and then compares for every aggregated row if this row equals the MAX/MIN (that's why I used the aggr function).

Cheers, Lukas

Find min and max value in a column

Fri, 26 Feb 2010 10:48:46 GMT

That is! Thank you very muck, Lukas!