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: 
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.

 

KeyProduct NumberItem NumberPoints Cost
1001|110011560.00
1001|210012560.00
1001|310013560.00
1001|4100140100.00
1001|5100150136.73
1001|610016560.00
1001|710017150138.00
1001|810018105113.00
1001|910019522.21
1001|10100110150138.00
1001|11100111150138.00
1001|1210011249953.00
1001|13100113548.00
1001|141001140136.73
1001|151001150136.73
1001|16100116700411.11

 

Key Product NumberItem NumberPoints Cost
2001|12001115048
2001|220012050
2001|320013100165
2001|420014150185
2001|520015150185
2001|620016560
2001|720017150180
2001|820018150200
2001|920019560
2001|10200110150170
2001|11200111560
2001|12200112100165
2001|13200113100165
2001|14200114100213.81
2001|15200115560

 

Thanks in advance, Simon

8 Replies
MK_QSL
MVP
MVP

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

marcus_sommer

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

swuehl
MVP
MVP

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.

Not applicable
Author

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
Author

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

Not applicable
Author

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

swuehl
MVP
MVP

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
Author

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