topic Re: Will interval match work for a given complex Price Calculation ? in QlikView

Will interval match work for a given complex Price Calculation ?

shekhar_analyti — Fri, 20 Mar 2020 12:24:02 GMT

Hi All ,

I am just not sure about the exact script which would dynamically calculate the price for number of item purchased .

Range Table

Nbr of Item lower limit	Nbr of Item Upper limit	Price Per Item	Int_Range
0	5	100	5
6	10	50	5
11	18	20	8
19	24	5	6
25	Ownwards	1

Manual steps of Pricing calculation

Number of Item Purchased	Calculation Tree	Sum Steps	Total Price
4	4*100	400	400
6	5100+150	500+50	550
10	5100+550	500+250	750
11	5100+550+1*20	500+250+20	770
16	5100+550+6*20	500+250+120	870
19	5100+550+820+15	500+250+160+5	935
27	5100+550+820+65+1*1	500+250+160+30+1	941

Using above , i need to calculate the price for list of nbr of items below

Count of Item Purchased	Price
8	?
17	?
25	?
29	?
40	?

Re: Will interval match work for a given complex Price Calculation ?

shekhar_analyti — Fri, 20 Mar 2020 12:27:27 GMT

Adding all big shots

@sunny_talwar @tresB @marcus_sommer

Re: Will interval match work for a given complex Price Calculation ?

marcus_sommer — Fri, 20 Mar 2020 15:37:48 GMT

I think it looked more complicated as it is - just try the following:

map:
mapping load Item, rangesum([Price Per Item], previous([Price Per Item])) as Price;
load [Nbr of Item lower limit] + iterno() as Item, [Price Per Item]
while [Nbr of Item lower limit] + iterno() <= [Nbr of Item Upper limit];
load * inline [
Nbr of Item lower limit Nbr of Item Upper limit Price Per Item
0 5 100
6 10 50
11 18 20
19 24 5
25 100 1
] (txt, delimiter is \t);

final: load *, applymap('map', [Count of Item Purchased], 0) as Price from Source;

- Marcus

Re: Will interval match work for a given complex Price Calculation ?

shekhar_analyti — Fri, 20 Mar 2020 16:22:33 GMT

Thank You Marcus . But with my lil modification it didnt work . Request you to attach a sample qvw .

Re: Will interval match work for a given complex Price Calculation ?

marcus_sommer — Sat, 21 Mar 2020 08:01:27 GMT

Yes, there is a small bug with the previous() which needs to be replaced with peek() and also iterno() needs to be reduced to one - therefore try it with:

temp:
load Item, rangesum([Price Per Item], peek('Price')) as Price;
load [Nbr of Item lower limit] + iterno() - 1 as Item, [Price Per Item]
while [Nbr of Item lower limit] + iterno() - 1 <= [Nbr of Item Upper limit];
load * inline [
Nbr of Item lower limit Nbr of Item Upper limit Price Per Item
1 5 100
6 10 50
11 18 20
19 24 5
25 100 1
] (txt, delimiter is \t);

map: mapping load * resident temp;
drop tables temp;

final:
load *, applymap('map', [Count of Item Purchased], 0) as Price;
load * inline [
Count of Item Purchased
8
17
25
29
40
];

- Marcus

Re: Will interval match work for a given complex Price Calculation ?

shekhar_analyti — Sat, 21 Mar 2020 12:20:19 GMT

Thank You Marcus ...

Wow this worked exactly i needed