# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for
Did you mean:
Specialist

## Will interval match work for a given complex Price Calculation ?

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 ?

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary

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

5 Replies
Highlighted
Specialist

Adding all big shots

Highlighted
MVP & Luminary

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

Highlighted
Specialist

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

Highlighted
MVP & Luminary

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

Highlighted
Specialist

Thank You Marcus ...

Wow this worked exactly i needed