Will interval match work for a given complex Price Calculation ?

[shekhar_analyti]

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	5*100+1*50	500+50	550
10	5*100+5*50	500+250	750
11	5*100+5*50+1*20	500+250+20	770
16	5*100+5*50+6*20	500+250+120	870
19	5*100+5*50+8*20+1*5	500+250+160+5	935
27	5*100+5*50+8*20+6*5+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	?

[shekhar_analyti]

Adding all big shots 

@sunny_talwar @tresesco @marcus_sommer 

[marcus_sommer]

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

[shekhar_analyti]

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

[marcus_sommer · Accepted solution]

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

[shekhar_analyti]

Thank You Marcus ... 

Wow this worked exactly i needed