QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Valued Contributor III

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
MVP

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

NoConcatenate
Join
Right Join
[Box ID]
Where \$(vFits3D([tabPackage.Dimensions 1],[tabPackage.Dimension 2],[tabPackage.Dimension 3],[tabBox.Dimension 1],[tabBox.Dimension 2],[tabBox.Dimension 3]));
Right Join
Min([Box price]) as [Box price]
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

14 Replies
Valued Contributor III

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.

MVP

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

MVP

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

MVP

mere liye kuch rakha kar

MVP

Agli bar

Valued Contributor III

kush141087

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

Do you have any insights?

MVP

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

MVP

How many possible combinations of BoxID possible?

MVP

May be like this:

Table1:

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

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:

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)