Packaging business case

[MindaugasBacius]

Lets assume database contains a package number and dimensions:

Parcel ID	Dimensions 1	Dimension 2	Dimension 3
1	10	20	30
2	15	25	10
3	10	30	20
4	15	15	25

There is another table with data of packaging box:

Box ID	Dimension 1	Dimension 2	Dimension 3	Box price
1	10	20	25	1
2	20	35	10	1,25
3	30	10	20	1,5
4	15	30	15	1,25

Result:

Parcel ID	Box ID	Box price
1	2	1,25
2	1	1
3	2	1,25
4	3	1,25

The packages need to be packed into the boxes:

1. The box need to be bigger or equal to the package dimensions (ex. package1 10 x 20 x 30 fits into the boxes that are 20 x 35 x 10, 30 x 10 x 20).
2. The box have to be cheapest among all the boxes that are possible. If package fits to few boxes with the same price pick the smaller box according to the box volume. (ex. package2 15 x 25 x 10, fits into the boxes that are 20 x 35 x 10, 15 x 30 x 15 for 1,25. Pick the 15 x 30 x 15)

Any suggestions or ideas would be very much appreciated!

[MindaugasBacius]

Now I do understand where is your point.

The amounts aren't huge. There are around 50 BoxIDs' and 10.000 parcels a year.

[sunny_talwar]

I have messed up the calculations, I will revisit this once I get time

[Kushal_Chawda]

Then I think Cross join will not be an overhead. You can go for that.

[sunny_talwar]

Fixed version using if statement:

Table1:

LOAD *,

  If(RangeMax([Dimension 1], [Dimension 2], [Dimension 3]) <= 25 and

    RangeMin(RangeMax([Dimension 1], [Dimension 2]), RangeMax([Dimension 3], [Dimension 2]), RangeMax([Dimension 1], [Dimension 3])) <= 20 and

    RangeMin([Dimension 1], [Dimension 2], [Dimension 3]) <= 10, 1,

  If(RangeMax([Dimension 1], [Dimension 2], [Dimension 3]) <= 35 and

    RangeMin(RangeMax([Dimension 1], [Dimension 2]), RangeMax([Dimension 3], [Dimension 2]), RangeMax([Dimension 1], [Dimension 3])) <= 20 and

    RangeMin([Dimension 1], [Dimension 2], [Dimension 3]) <= 10, 2,

  If(RangeMax([Dimension 1], [Dimension 2], [Dimension 3]) <= 30 and

    RangeMin(RangeMax([Dimension 1], [Dimension 2]), RangeMax([Dimension 3], [Dimension 2]), RangeMax([Dimension 1], [Dimension 3])) <= 15 and

    RangeMin([Dimension 1], [Dimension 2], [Dimension 3]) <= 15, 4, 3))) as [Box ID];

LOAD * INLINE [

    Parcel ID, Dimension 1, Dimension 2, Dimension 3

    1, 10, 20, 30

    2, 15, 25, 10

    3, 10, 30, 20

    4, 15, 15, 25

];

Table2:

LOAD * INLINE [

    Box ID, Box Dimension 1, Box Dimension_2, Box Dimension 3, Box Price

    1, 10, 20, 25, 1

    2, 20, 35, 10, "1.25"

    3, 30, 10, 20, "1.5"

    4, 15, 30, 15, "1.25"

];

Left Join (Table1)

LOAD [Box ID],

  [Box Price]

Resident Table2;

DROP Table Table2;.

[MarcoWedel · Accepted solution]

Hi,

another solution might be:

SET vFits2D = ($1<=$3 and $2<=$4 or $1<=$4 and $2<=$3);
SET vFits3D = ($1<=$4 and $(vFits2D($2,$3,$5,$6)) or $1<=$5 and $(vFits2D($2,$3,$4,$6)) or $1<=$6 and $(vFits2D($2,$3,$4,$5)));

QUALIFY [Dimensions 1], [Dimension 1], [Dimension 2], [Dimension 3];

tabPackage:
LOAD * FROM [https://community.qlik.com/thread/218933] (html, codepage is 1252, embedded labels, table is @1);

tabBox:
LOAD * FROM [https://community.qlik.com/thread/218933] (html, codepage is 1252, embedded labels, table is @2);

tabLink:
NoConcatenate
LOAD * Resident tabPackage;
Join
LOAD * Resident tabBox;
Right Join
LOAD [Parcel ID],
    [Box ID]
Resident tabLink
Where $(vFits3D([tabPackage.Dimensions 1],[tabPackage.Dimension 2],[tabPackage.Dimension 3],[tabBox.Dimension 1],[tabBox.Dimension 2],[tabBox.Dimension 3]));
Right Join
LOAD [Parcel ID],
    Min([Box price]) as [Box price]
Resident tabLink
Group By [Parcel ID];

DROP Fields [tabBox.Dimension 1],[tabBox.Dimension 2],[tabBox.Dimension 3],[tabPackage.Dimensions 1],[tabPackage.Dimension 2],[tabPackage.Dimension 3],[Box price] From tabLink;

hope this helps

regards

Marco