Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
MindaugasBacius
Valued Contributor 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!

Tags (1)
1 Solution

Accepted Solutions

Re: Packaging business case

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
Valued Contributor III

Re: Packaging business case

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.

Re: Packaging business case

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

Re: Packaging business case

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

Re: Packaging business case

mere liye kuch rakha kar

Re: Packaging business case

Agli bar

MindaugasBacius
Valued Contributor III

Re: Packaging business case

kush141087

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

Do you have any insights?

Re: Packaging business case

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

Re: Packaging business case

How many possible combinations of BoxID possible?

Re: Packaging business case

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;