Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
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!

1 Solution

Accepted Solutions
marcus_sommer

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
marcus_sommer

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