topic Re: Packaging business case in QlikView

Packaging business case

MindaugasBacius — Tue, 31 May 2016 06:00:41 GMT

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:

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).
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

MindaugasBacius — Tue, 31 May 2016 10:02:38 GMT

How could I get the table like this:

Parcel ID	P.Dim1	P.Dim2	P.Dim3	Box ID	B.Dim1	B.Dim2	B.Dim3	Box price
1	10	20	30	1	10	20	25	1
1	10	20	30	2	20	35	10	1,25
1	10	20	30	3	30	10	20	1,5
1	10	20	30	4	15	30	15	1,25
2	15	25	10	1	10	20	25	1
2	15	25	10	2	20	35	10	1,25
...	...	...	...	...	...	...	...	...

There is no common field.

Re: Packaging business case

sunny_talwar — Tue, 31 May 2016 10:08:26 GMT

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

Re: Packaging business case

Kushal_Chawda — Tue, 31 May 2016 10:16:35 GMT

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

Re: Packaging business case

Kushal_Chawda — Tue, 31 May 2016 10:22:01 GMT

mere liye kuch rakha kar

Re: Packaging business case

sunny_talwar — Tue, 31 May 2016 10:23:07 GMT

Agli bar

Re: Packaging business case

MindaugasBacius — Tue, 31 May 2016 10:34:26 GMT

kush141087

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

Do you have any insights?

Re: Packaging business case

Kushal_Chawda — Tue, 31 May 2016 10:38:49 GMT

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

sunny_talwar — Tue, 31 May 2016 10:39:15 GMT

How many possible combinations of BoxID possible?

Re: Packaging business case

sunny_talwar — Tue, 31 May 2016 10:48:19 GMT

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

MindaugasBacius — Tue, 31 May 2016 10:57:40 GMT

Now I do understand where is your point.

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

Re: Packaging business case

sunny_talwar — Tue, 31 May 2016 11:38:30 GMT

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

Re: Packaging business case

Kushal_Chawda — Tue, 31 May 2016 11:42:42 GMT

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

Re: Packaging business case

sunny_talwar — Tue, 31 May 2016 12:47:53 GMT

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

Re: Packaging business case

MarcoWedel — Tue, 31 May 2016 22:30:03 GMT

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