Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
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 limitNbr of Item Upper  limitPrice Per ItemInt_Range
051005
610505
1118208
192456
25Ownwards1 

 

Manual steps of Pricing calculation 

Number of Item PurchasedCalculation TreeSum StepsTotal Price
44*100400400
65*100+1*50500+50550
105*100+5*50500+250750
115*100+5*50+1*20500+250+20770
165*100+5*50+6*20500+250+120870
195*100+5*50+8*20+1*5500+250+160+5935
275*100+5*50+8*20+6*5+1*1500+250+160+30+1941

 

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?

 

TREE.PNG

 

1 Solution

Accepted Solutions
MVP & Luminary
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

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

 

Adding all big shots 

@sunny_talwar @tresesco @marcus_sommer 

Highlighted
MVP & Luminary
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
Specialist

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

TREE2.PNG

MVP & Luminary
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

View solution in original post

Highlighted
Specialist
Specialist

Thank You Marcus ... 

Wow this worked exactly i needed