# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Complex Sort Calculation in Script

Hi,

I'm hoping that one of you will be able to help me with a really complex sort in my script (or so I think). I'm trying to work out how to calculate the most economical way to get to a number of points.

I've copied 2 examples below.

In the first example (Product 1001) I want in this instance to be able to get over 700 points as economically as possible. I can see the answer is to pick items 7, 8 & 12 but I cannot get Qlikview to work that out for me.

In the second example (Product 2001) I want in this instance to be able to get over 250 points as economically as possible. I can see the answer is to pick items 1 & 3 but again I cannot get Qlikview to work that out for me.

 Key Product Number Item Number Points Cost 1001|1 1001 1 5 60.00 1001|2 1001 2 5 60.00 1001|3 1001 3 5 60.00 1001|4 1001 4 0 100.00 1001|5 1001 5 0 136.73 1001|6 1001 6 5 60.00 1001|7 1001 7 150 138.00 1001|8 1001 8 105 113.00 1001|9 1001 9 5 22.21 1001|10 1001 10 150 138.00 1001|11 1001 11 150 138.00 1001|12 1001 12 499 53.00 1001|13 1001 13 5 48.00 1001|14 1001 14 0 136.73 1001|15 1001 15 0 136.73 1001|16 1001 16 700 411.11

 Key Product Number Item Number Points Cost 2001|1 2001 1 150 48 2001|2 2001 2 0 50 2001|3 2001 3 100 165 2001|4 2001 4 150 185 2001|5 2001 5 150 185 2001|6 2001 6 5 60 2001|7 2001 7 150 180 2001|8 2001 8 150 200 2001|9 2001 9 5 60 2001|10 2001 10 150 170 2001|11 2001 11 5 60 2001|12 2001 12 100 165 2001|13 2001 13 100 165 2001|14 2001 14 100 213.81 2001|15 2001 15 5 60

8 Replies
Not applicable

## Re: Complex Sort Calculation in Script

Can you provide logic here as well? How 7, 8 and 12 for 1st Instance?

Not applicable

## Re: Complex Sort Calculation in Script

Your logic isn't quite clear to me but I assume that you need Peek() or Previous() ? within a sorted resident load to compare, match and/or calculate the current records with previous records.

- Marcus

Not applicable

## Re: Complex Sort Calculation in Script

That's more like an optimization problem than a sorting issue

I assume the economic aspect is determined by Points per Cost, right?

INPUT:

LOAD *, Points / Cost  as PpC;

[Product Number],

[Item Number],

Points,

Cost

FROM

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *, If(Alt(Peek(Acc),0) <= 700,1,0) as PickThisItem;

If(Peek([Product Number])<>[Product Number], Points, Rangesum(Peek(Acc), Points)) as Acc

RESIDENT INPUT

ORDER BY [Product Number], PpC desc;

DROP TABLE INPUT;

But this will not give you a solution with the least cost to achieve the 700 points, it will just pick the items that have the best ratio until 700 points are reached (but in this example, you will get a lot more points than needed and hence pay more than needed to achieve your goal).

There are some optimization algorithms that can solve this issue, but I think QV load script  is not the best way to solve it.

Not applicable

## Re: Complex Sort Calculation in Script

Hi Manish, I chose 7,8 & 12 as they add up to 754 points at a cost of £304.00. This is the cheapest way of getting my points score over 700.

Thanks, Simon

Not applicable

## Re: Complex Sort Calculation in Script

Hi Stefan,

Thanks for your reply. The points per cost method was my 1st instinct with this, but as you explain it does not give me the desired result.

You mentioned "Optimization Algorithms" that can resolve my issue, where would I look to find such wizardry.

To give a little more background, I take in data from several optimized QVD files and perform various calculations around that data, which I then have to export as a csv file so that it can be imported into a 3rd party piece of software which is used by the end user.

Not applicable

## Re: Complex Sort Calculation in Script

Hi Marcus,

Yes I have already used both peek() and previous () on my existing calculations successfully, however the issue I have is that I cannot get them to sort in the correct order to give the correct results.

Thanks, Simon

Not applicable

## Re: Complex Sort Calculation in Script

Hi Simon,

a common sort of your table will not give you the requested result, I believe.

A sort looks at two values and determines which value precedes the other (similar to what I've done with the PpC column), but in your case, you can't determine which value would be to preferred / picked by just looking at the two values, you need to know about all the other values.

It's a set optimization problem, you need to compare / sort different sets (or configurations, if you want) of your items.

A brute force algorithm would be to create all possible distinct (sub)sets of your items, drop the sets with summed Points < X.

Sort by the summed Cost of your set descending and pick the top ranking set.

There are more sophistacated algorithms and problem descriptions, e.g.

Linear programming - Wikipedia, the free encyclopedia

Integer programming - Wikipedia, the free encyclopedia

Not applicable

## Re: Complex Sort Calculation in Script

Thanks Stefan, looks like I have some reading to do. Simon

Community Browser