I am building a delivery cost tracking report. We have all deliveries, quantities etc in the system, but we are missing the delivery cost itself. I have in excel file the delivery cost table which I would now need to link to my datamodel. The problem is that cost table is based on ranges and I would like Qlikview to searc the right range and the price.
I made a simple example below about the data I have, which hopefully clarifies my problem
Ship to PLZ
Delivery cost table
Ship to PLZ
Quantity from (kg)
Quantity to (kg)
Cost per delivery (eur)
What kind of script I would need to return delivery cost of 200€ for delivery 111111, 300€ for 111111... ?
Thanks your reply Thomas, but I think this is not yet solution for me. In example I put in this discussion, I had only one postal code in the pricing table example, but actually I have 93 different postal code in my table. So what the script should do is first search the right set of ranges with the postal code and then within that range the right cost for the quantity delivered. I think the example you made, works only in case of one postal code
Your addition to script seems to work very nicely, thanks! However, I found out one thing that is causing some complexity and I don't know how to handle that.
I have two different set of ranges in my data. In one country there is own ranges and then there is another ranges which is shared by all orher countries. So my question would be, would you have any idea how to integrate some kind of if-formula into the interval match? Script should do sfollowing, in case of this one specific country, do the interval match with range 1 and if it is any other country, do the interval macth in range 2.
Would you have idea how to tackle this remaining issue?