topic Complex Sort Calculation in Script in QlikView

Complex Sort Calculation in Script

Mon, 08 Aug 2016 16:30:26 GMT

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

Thanks in advance, Simon

Re: Complex Sort Calculation in Script

MK_QSL — Mon, 08 Aug 2016 16:39:20 GMT

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

Re: Complex Sort Calculation in Script

marcus_sommer — Mon, 08 Aug 2016 17:19:59 GMT

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

swuehl — Mon, 08 Aug 2016 17:58:32 GMT

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

Tue, 09 Aug 2016 08:26:56 GMT

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

Tue, 09 Aug 2016 08:50:55 GMT

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

Tue, 09 Aug 2016 08:55:59 GMT

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

swuehl — Tue, 09 Aug 2016 20:31:29 GMT

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

Re: Complex Sort Calculation in Script

Wed, 10 Aug 2016 10:47:40 GMT

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