Data sort and drop in QlikView LOAD

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Data sort and drop in QlikView LOAD

marcus_sommer — Fri, 23 Feb 2018 12:26:54 GMT

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

topic Re: Data sort and drop in QlikView LOAD in QlikView

Data sort and drop in QlikView LOAD

Re: Data sort and drop in QlikView LOAD