Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Pricing Model

Dear community,

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?

Example Below:

Table 1

BookingWeightServicePackage Type
15ExpressBox
210EconomyParcel
350ExpressParcel

Table 2

SupplierWeight FromWeight ToServicePackage TypeCost
Supplier A010ExpressBox£5
Supplier B050ExpressBox£10

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Pricing Model

I've added a QuoteID and also a JOIN to the Intervalmatch to get the data model a bit more slim.

Looks quite ok to me.

View solution in original post

13 Replies
MVP
MVP

Re: Pricing Model

Try for example an INTERVALMATCH:

INTERVALMATCH(Weight, Service, [Package Type])

LOAD [WeightFrom],[Weight To], Service, [Package Type]

RESIDENT YourRateTable;

IntervalMatch

Not applicable

Re: Pricing Model

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?

Rates:
LOAD Supplier,
Area,
Service,
prczone,
method,
level,
type,
goodtype,
[Weight Start],
[Weight Finish],
[Min Weight],
[Max Weight],
[Base Cost],
[Additional KG]
FROM
[\\qlikview\Qlikview Documents\Excel Lookup Tables\Rate Comparison.xlsx]
(
ooxml, embedded labels, table is [UK Rates]);

Quotes:
LOAD * INLINE [
Weight, level, type
5, 74, parcel
10, 74, parcel
30, 74, parcel
75, 74, parcel
150, 74, parcel
300, 74, parcel
]
;

TmpBridgeTable:
IntervalMatch(Weight, level, type)
Load distinct
[Min Weight],
[Max Weight],
level,
type
Resident Rates;

MVP
MVP

Re: Pricing Model

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)?

Not applicable

Re: Pricing Model

Attached is the "Rates" sheet - at the moment I'm just using that in line load to check it works!

Not applicable

Re: Pricing Model

In doing this I can see my inline is "parcel" not "Parcel"

Thanks for your help though!

Not applicable

Re: Pricing Model

With that change my script above only seems to be joining the "level" and "type" and not taking into account the weight breaks

MVP
MVP

Re: Pricing Model

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?

Not applicable

Re: Pricing Model

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

MVP
MVP

Re: Pricing Model

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)