Finding a value based on multiple criteria

[#1]

Hello all,

I am building an analysis of shipping costs for a company as my first project.

My target is to provide accurate shipping and handling costs on line and consignment level for all parcels sent by the company.

I'm 90% done and happy with getting nice and consistent results..

But there is one problem I can't solve by myself or google.

The company is using a variety of shipping agents, who offer a variety of shipping services, to a variety of countries, for a variety of uneven weight ranges. These costs are not in the system, they reside in excel/pdf/email files.

I have got all the price lists together and created a table to cater for all service-weight-country-price combinations (which in total is 2,5k lines long)

Please find the xlsx file with an example range attached to this post.

Now in my facts data I have worked out shipping agent service, country and calculated weight for each consignment.. Now I need to find the shipping cost on that table depending on those 3 factors.

Any help? I'm lost.

I was hoping to do that in the script rather than set analysis, however any solution which will allow me to deliver will be good.

[Gysbert_Wassenaar]

If I understand you correctly you have two tables that have three fields in common. You can simply use these fields to link the tables by making sure the fields have the same case sensitive names in both tables. What you can also do is the create a key field in both tables from these three fields so the two tables are linked using that new key field.

Facts:

LOAD

     ...some fields...

     ShippingAgentService,

     Country,

     Weight,

     Autonumber(ShippingAgentService&'|'&Country&'|'&Weight) as %KEY

FROM ...

PriceList:

LOAD

     ShippingAgentService,

     Country,

     Weight,

     Autonumber(ShippingAgentService&'|'&Country&'|'&Weight) as %KEY,

     Price

FROM ...the_excel_file..

DROP FIELDS ShippingAgentService, Country,  Weight FROM TABLE Facts;

edit: fixed autonumber lines

[#3]

Hi,

Thank you very much for your reply.

I have tried AutoNumber however it seems to be only accepting 1 or 2 parameters, and we just tried using 3

Now, assuming we will successfully create a key by connecting fields, the weight is a bit of a problem.

A parcel can have any weight, and shipping prices have weight brackets - which will never have an exact match.

The Facts table has exact weight value, the table we look up in has weight brackets - which are different for each shipping agent service.

Example:

A parcel weight is 645g

One shipping agent has weight brackets with one price for parcels up to 1000g for £1 and another for parcels above 2000g for £2

Parcel falls in 1st bracket so the price is £1

Another shipping agent will have different weight brackets, let's say each 250g for £1,  500g for £2 and  750g for £3

So, parcel being withing the 750g bracket would cost £3..

Any ideas on how this can be looked up?

I had an idea of rounding up the parcel weight to nearest 250g and creating price entries for each service and country for each 250g bracket in my price table.. however this seems to be a lot of work, and will probably prove to be a real nightmare when updating prices..

[Gysbert_Wassenaar]

Duh, yeah, my mistake. The autonumberhash functions take lots of parameters. The autonumber function needs a concatenated string and optionally a name for the internal counter. Try Autonumber(ShippingAgentService & '|' & Country & '|' & Weight) as %KEY

[#5]

Happy days!

So I have a key based on

     AutoNumber([Service]&'|'&[Country]&'|'&[Weight]) as %PRICEKEY,

I understand that a key is created for every combination of existing values on the table with shipments

and another key like that is created for every combination of existing values on the table with pricing

one last issue remains here - which I had described in my post above

Weights in table with consignments are actual consignment weight.

and weights on pricing table are ranges. So let's say for up to 1000g price is £1, up to 2000g price is £2 so in 99% there will be no match on the key..

So what I have now is a huge step forward to where I were, however how can I match these further?

Thanks!

[Gysbert_Wassenaar]

Ok, so basically you need to match the weight of a 'service' with an interval of weight in the pricelist. That's what the intervalmatch function can do for you. See this blog post for more information: IntervalMatch. I think you'll need to use the extended syntax of the intervalmatch function and use Service and Country as additional parameters to make sure you match the weight interval with the correct Service and Country combination. Something like this:

Facts:

LOAD

     ...some fields...

     ShippingAgentService,

     Country,

     Weight

FROM ...

PriceList:

LOAD

     ShippingAgentService,

     Country,

     Weight_From,

     Weight_To,

     Price

FROM ...the_excel_file..

WeightIntervals:

IntervalMatch(Weight,ShippingAgentService,Country)

LOAD

     Weight_From,

     Weight_To,

     ShippingAgentService,

     Country

Resident PriceList;

[#7]

Thank you.

code:

Facts:

LOAD OrderNo,

    ShippingAgentService,

    Country,

    Weight

FROM

Parcels.xlsx

(ooxml, embedded labels, table is Sheet1);

PriceList:

LOAD ShippingAgentService,

    Country,

    Weight_From,

    Weight_To,

    Price

FROM

PriceList.xlsx

(ooxml, embedded labels, table is Sheet1);

WeightIntervals:

IntervalMatch(Weight,ShippingAgentService,Country)

LOAD

    Weight_From,

    Weight_To,

    ShippingAgentService,

    Country

Resident PriceList;

Creates this:

And does not give me the price related to the OrderNo

I've tried various ways of doing that and I even had some success, however the pricing brought in was wrong.

I have attached the qvw file and 2 files with mock data I was using to figure out how it works.. 😕

Any ideas?

[Gysbert_Wassenaar]

Ok, try joining the lot to the fact table:

Facts:
LOAD OrderNo,
    ShippingAgentService,
    Country,
    Weight
FROM
comm196665_Parcels.xlsx
(ooxml, embedded labels, table is Sheet1);

PriceList:
LOAD ShippingAgentService,
    Country,
    Weight_From,
    Weight_To,
    Price
FROM
comm196665_PriceList.xlsx
(ooxml, embedded labels, table is Sheet1);

Join (Facts)
IntervalMatch(Weight,ShippingAgentService,Country)
LOAD
    Weight_From,
    Weight_To,
    ShippingAgentService,
    Country
Resident PriceList;

Join(Facts)
LOAD * Resident PriceList;

Drop Table PriceList;

[effinty2112]

Hi Robert,

                    I hope I've interpreted what you need. Have a look at the attached qvw. Pick the country and Shipping Agent in the listboxes, enter the Consignment Weight in the Input Box and the Straight Table headed 'Price' will try to match a price from your price table. if you enter a weight that is absent it will give the closest price, above and below the desired weight if possible.

Cheers

Andrew

[MarcoWedel]

Hi,

one solution maybe could be:

Facts:
LOAD AutoNumberHash128(ShippingAgentService,Country,Weight) as %Key1,
    OrderNo,
    ShippingAgentService,
    Country,
    Weight
FROM [https://community.qlik.com/servlet/JiveServlet/download/937097-202714/Parcels.xlsx] (ooxml, embedded labels, table is Sheet1);

PriceList:
LOAD AutoNumberHash128(ShippingAgentService,Country,Weight_From,Weight_To) as %Key2,
    ShippingAgentService,
    Country,
    Weight_From,
    Weight_To,
    Price
FROM [https://community.qlik.com/servlet/JiveServlet/download/937097-202715/PriceList.xlsx] (ooxml, embedded labels, table is Sheet1);

WeightIntervals:
IntervalMatch(Weight,ShippingAgentService,Country)
LOAD Weight_From,
    Weight_To,
    ShippingAgentService,
    Country
Resident PriceList;

Join (WeightIntervals)
LOAD Distinct
    AutoNumberHash128(ShippingAgentService,Country,Weight) as %Key1,
    ShippingAgentService,
    Country,
    Weight
Resident Facts;

DROP Fields ShippingAgentService, Country From Facts;

Join (WeightIntervals)
LOAD Distinct
    AutoNumberHash128(ShippingAgentService,Country,Weight_From,Weight_To) as %Key2,
    ShippingAgentService,
    Country,
    Weight_From,
    Weight_To
Resident PriceList;

DROP Fields ShippingAgentService, Country From PriceList;

Left Join (WeightIntervals)
LOAD Distinct
    AutoNumberHash128(ShippingAgentService,Country,Weight) as %Key1,
    ShippingAgentService,
    Country,
    Weight
Resident WeightIntervals;

Left Join (WeightIntervals)
LOAD Distinct
    AutoNumberHash128(ShippingAgentService,Country,Weight_From,Weight_To) as %Key2,
    ShippingAgentService,
    Country,
    Weight_From,
    Weight_To
Resident WeightIntervals;

DROP Field Weight, Weight_From, Weight_To From WeightIntervals;

hope this helps

regards

Marco