topic Re: Finding Top 5 values in 10 columns in App Development

Finding Top 5 values in 10 columns

gurkan — Wed, 28 May 2025 19:39:45 GMT

I have 10 columns containing number values and need to find and sum the top 5 values for each row. How can I do it in the data loading script?

Re: Finding Top 5 values in 10 columns

-SW- — Wed, 28 May 2025 21:30:10 GMT

Without see-ing sample data, it will be hard to provide an appropriate answer.
Have you tried looking at the FirstSortedValue() Function?

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/AggregationFunctions/firstsorted.htm

Re: Finding Top 5 values in 10 columns

seanbruton — Wed, 28 May 2025 21:45:53 GMT

Hi There,

I would maybe load this into a table using a crosstable function and resident load separately with a sort function. Then this can make the data dimensional and a measure with easy formulas. Like SW then mentioned use the firstsortedvalue() function.

Regards

Sean

Re: Finding Top 5 values in 10 columns

gurkan — Thu, 29 May 2025 08:19:25 GMT

I want to give some examples below. I aim to find the top 5 values in the 10-column table and calculate the Sum of the Top 5 while loading the data. Thank you for your help.

Re: Finding Top 5 values in 10 columns

-SW- — Thu, 29 May 2025 15:52:32 GMT

If I was to assume that is your source table, you will need some sort of qualifier first.

Maybe use Rowno() as ID, then load the rest of your data in.

Then follow suit with SeanBruton response with cross table, and then do firstsortedvalue functions to pull what you need into a summarised table.

But tbh, you better off not using firstsortedvalue in the script and better off using the aggr() and Rank functions in the front end after doing cross table load. It be much more dynamic and usable dependent on selections etc

Re: Finding Top 5 values in 10 columns

gurkan — Fri, 30 May 2025 08:27:04 GMT

Thank you , I will try it.

Re: Finding Top 5 values in 10 columns

seanbruton — Sun, 01 Jun 2025 00:08:39 GMT

Example solution:

Gurkan,

Thanks for the example. My apologies for the delay I was out of town.

My solution example below with script:

Dataset_Org:

LOAD * INLINE [

Table|Row|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|Col9|Col10
Tbl|R1|1|20|3|2|18|5|7|10|13|2
Tbl|R2|10|25|38|15|42|65|22|18|100|11
] (delimiter is '|');

Dataset_Cross_tmp:

CROSSTABLE (Col_Name,Col_Value,2)
LOAD * Resident Dataset_Org;

drop table Dataset_Org;

Dataset_Cross_final:

LOAD *, IF(RANK <= 5, 1,0) AS Rank_ind;

LOAD *, AutoNumber(Col_Value,Row) as RANK
RESIDENT Dataset_Cross_tmp
ORDER BY Row,Col_Value DESC;

DROP TABLE Dataset_Cross_tmp

I hope this helps. Please note I added Fields Table and Row as dimensional segmentation, You can use rowno function in place of Row Field. I have also attached the qvf file.

Regards

Sean

Re: Finding Top 5 values in 10 columns

gurkan — Mon, 02 Jun 2025 07:48:09 GMT

That works thank you so much for your help.