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!
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
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.
This is just a cross Join of the two data set (join with no fields matching)
Simply join the two tables, which is cross join to get this table, but you should avoid cross join
mere liye kuch rakha kar
Agli bar
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
How many possible combinations of BoxID possible?
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;