topic Re: Select top/bottom 5 rows in script depending on variable in QlikView

Select top/bottom 5 rows in script depending on variable

puttemans — Mon, 27 Oct 2014 13:00:46 GMT

Hi all,

I have a file containing thousands of records that can be ordered following a given '% Key to MOAR' (KtM). Per individual KtM, there are still possibly hundreds of records/lines possible.

I would like to cleanup the original file, and make sure that I end up per KtM with a maximum of 5 records/lines, based upon another variable in the set I created, being UniqueKey. Only the 5 lines that have the lowest(fastest) travel time should be selected per KtM. This is the first value in the UniqueKey variable. (notice in the example I need the UniqueKey measure for the top 5, as there is more than 1 train running in the same time between the selected KtM) I managed to create a variable that indicates how many lines per KtM need to be selected, as not all KtM have 5 lines (some have less, and they should then all be taken into account). The Traveltime variable is changing for every KtM, so working with a fixed number is impossible.

Could anyone show me possible routes to a solution?

Thank you

Re: Select top/bottom 5 rows in script depending on variable

Anonymous — Mon, 27 Oct 2014 14:21:45 GMT

Hello Johan,

The simplest would be to

1 - Order your by KtM, by Travel Time ascending to obtain the appropriate sequence

2 - tag your routes with an index that restarts for every KtM

3 - store the table

4 - load the table for all indices that are < = 5

Hope this helps,

Antoine

Re: Select top/bottom 5 rows in script depending on variable

puttemans — Mon, 27 Oct 2014 14:52:16 GMT

Hi Antoine,

Thank you for the help. You made me round that hurdle already , some more challenges to go...