Data sort and drop in QlikView LOAD

[sculptorlv]

Hello again!

I am looking for new options for myself. I hope you can help me.

I have a BASIC table from SQL statement, like ..

SalePerson         /Order nr.      /Sum

Alex                    /AB13            /200$

Alex                    /BC33            /300$

Alex                    /FF11             /400$

Jone                    /FF23            /150$

etc...

I want from this BASIC table with help of LOAD create another table,

where the following condition will be performed:

     For each sales person, only 100 data rows (or less, if there is less than 100 data rows) remains with higher Sum values.

I need, to keep not more than 100 orders with highest profit for each sales persons.

I hope, it can be done.

Thank you in advance for your help!

[marcus_sommer · Accepted solution]

You could use interrecord-functions like Peek() or Previous() ? for it maybe with something like this:

load *, if(SalePerson = previous(SalePerson), rangesum(peek('Counter'), 1), 1) as Counter

resident Source order by SalePerson, Sum desc;

After this you could just load the table again with a where-clause like: where Counter <= 100 or maybe using it with a gui-expression like: sum({< Counter = {"<=100"}>} Sum)

- Marcus