Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Data sort and drop in QlikView LOAD

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!

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Data sort and drop in QlikView LOAD

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

View solution in original post

1 Reply
Highlighted
MVP & Luminary
MVP & Luminary

Re: Data sort and drop in QlikView LOAD

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

View solution in original post