Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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
Highlighted
MVP & Luminary
MVP & Luminary

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

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

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

 

Adding all big shots 

@sunny_talwar @tresesco @marcus_sommer 

Highlighted
MVP & Luminary
MVP & Luminary

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

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

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

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

TREE2.PNG

Highlighted
MVP & Luminary
MVP & Luminary

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

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

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

Thank You Marcus ... 

Wow this worked exactly i needed