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.
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.
That's more like an optimization problem than a sorting issue
I assume the economic aspect is determined by Points per Cost, right?
You can start with something like
LOAD *, Points / Cost as PpC;
(html, codepage is 1252, embedded labels, table is @1);
LOAD *, If(Alt(Peek(Acc),0) <= 700,1,0) as PickThisItem;
If(Peek([Product Number])<>[Product Number], Points, Rangesum(Peek(Acc), Points)) as Acc
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.
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.
Thanks for your help, 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.