
Re: Complex Sort Calculation in Script
Manish Kachhia Aug 8, 2016 12:39 PM (in response to Simon Baxter)Can you provide logic here as well? How 7, 8 and 12 for 1st Instance?

Re: Complex Sort Calculation in Script
Simon Baxter Aug 9, 2016 4:26 AM (in response to Manish Kachhia )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


Re: Complex Sort Calculation in Script
Marcus Sommer Aug 8, 2016 1:19 PM (in response to Simon Baxter)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

Re: Complex Sort Calculation in Script
Simon Baxter Aug 9, 2016 4:55 AM (in response to Marcus Sommer )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


Re: Complex Sort Calculation in Script
Stefan Wühl Aug 8, 2016 1:58 PM (in response to Simon Baxter)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
INPUT:
LOAD *, Points / Cost as PpC;
LOAD Key,
[Product Number],
[Item Number],
Points,
Cost
FROM
[https://community.qlik.com/thread/228299]
(html, codepage is 1252, embedded labels, table is @1);
RESULT:
LOAD *, If(Alt(Peek(Acc),0) <= 700,1,0) as PickThisItem;
LOAD *,
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.

Re: Complex Sort Calculation in Script
Simon Baxter Aug 9, 2016 4:50 AM (in response to Stefan Wühl )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.
Thanks for your help, Simon

Re: Complex Sort Calculation in Script
Stefan Wühl Aug 9, 2016 4:31 PM (in response to Simon Baxter)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.

Re: Complex Sort Calculation in Script
Simon Baxter Aug 10, 2016 6:47 AM (in response to Stefan Wühl )Thanks Stefan, looks like I have some reading to do. Simon


