I have a table of orders which contain information on weight, service and package type.
I want to join these to a rate table that has the service, package type and a upper and lower weight limit. Is there a way I can do a join that matches the service and package type then picks up the correct weight bracket if the weight is greater than the min and less than the max?
|Supplier||Weight From||Weight To||Service||Package Type||Cost|
What I want to do is add a column for each supplier so Table 1 would have the price for supplier A on line 1 as £5 and a column for supplier 2 for line 1 of £10
Many thanks - Drew
Solved! Go to Solution.
I think you are on to something - any chance you can have a look at my script? I still don't fully understand how the intervalmatch function works.... for my example above. Supplier A would be created 10 times for every weight betweek 0 & 10?
[\\qlikview\Qlikview Documents\Excel Lookup Tables\Rate Comparison.xlsx]
(ooxml, embedded labels, table is [UK Rates]);
LOAD * INLINE [
Weight, level, type
5, 74, parcel
10, 74, parcel
30, 74, parcel
75, 74, parcel
150, 74, parcel
300, 74, parcel
IntervalMatch(Weight, level, type)
The code should create a link table that for example links Booking 1 to both supplier (because Service and Package Type matches and Weight falls in range interval for both supplier.
If you create a table box with Booking and Supplier, you should see both listed for Booking 1.
If not, then we might need to look at the code / input data. Could you upload some sample records (e.g. your Excel file or a file with same structure / mockup data)?
How are your weight breaks defined in your rate table? I see mostly a weight range 0 to 100000 (sometimes 0 to 10 or 0 to 100, but not clearly defined distinct weight intervals for Min and max Weight?
There each "level" and "type" there will be a weight range from 0-10 then 10-100 then 100-1000000 OR it might be 0-20 then 20-100 etc etc. each level and type should have it's own range and then a different range for each supplier
I don't see this ranges except for supplier F in your sample file, all other supplier always show Min weight 0 for all ranges.
Take care that your intervals don't overlap (i.e. not having a range 0 to 10 and 10 to 100, overlapping at 10), if you want to create a link to a single range. I guess one or the other range should exclude 10 (by adding or subtracting a small number from the one limit)