Finding Top 5 values in 10 columns

[gurkan]

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? 

[-SW-]

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/Aggrega...

[seanbruton]

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

[gurkan]

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.

[-SW-]

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

[gurkan]

Thank you , I will try it.

[seanbruton · Accepted solution]

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

[gurkan]

That works thank you so much for your help.