14 Replies Latest reply: May 31, 2016 6:30 PM by Marco Wedel RSS

    Packaging business case

    Mindaugas Bacius

      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!

        • Re: Packaging business case
          Mindaugas Bacius

          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
            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;

            • Re: Packaging business case
              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;.


              Capture.PNG

              • Re: Packaging business case
                Marco Wedel

                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