Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Any suggestions or ideas would be very much appreciated!
Now I do understand where is your point.
The amounts aren't huge. There are around 50 BoxIDs' and 10.000 parcels a year.
I have messed up the calculations, I will revisit this once I get time
Then I think Cross join will not be an overhead. You can go for that.
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;.
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