Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Packaging business case

Lets assume database contains a package number and dimensions:

Parcel IDDimensions 1Dimension 2Dimension 3
1102030
2152510
3103020
4151525

There is another table with data of packaging box:

Box IDDimension 1Dimension 2Dimension 3Box price
11020251
22035101,25
33010201,5
41530151,25



Result:

Parcel IDBox IDBox price
121,25
211
321,25
431,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!

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_218933_Pic1.JPG

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

View solution in original post

14 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

How could I get the table like this:

Parcel IDP.Dim1P.Dim2P.Dim3Box IDB.Dim1B.Dim2B.Dim3Box price

1

10203011020251
110203022035101,25
110203033010201,5
110203041530151,25
215251011020251
215251022035101,25
...........................

There is no common field.

sunny_talwar

This is just a cross Join of the two data set (join with no fields matching)

Kushal_Chawda

Simply join the two tables, which is cross join to get this table, but you should avoid cross join

Kushal_Chawda

mere liye kuch rakha kar

sunny_talwar

Agli bar

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

kush141087

I am not sure without cross table how to start solving my case.

Do you have any insights?

Kushal_Chawda

Yeah I can understand without cross join it will be difficult to achieve but not impossible too. I will try from my side but mean while if you have not huge data then use cross join approach

sunny_talwar

How many possible combinations of BoxID possible?

sunny_talwar

May be like this:

Table1:

LOAD *,

  If([Dimension 1] >= 10 and [Dimension 2] >= 20 and [Dimension 3] >= 25, 1,

  If([Dimension 1] >= 20 and [Dimension 2] >= 35 and [Dimension 3] >= 10, 2,

  If([Dimension 1] >= 15 and [Dimension 2] >= 30 and [Dimension 3] >= 15, 3, 4))) 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;