Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon guys, how are you?
I have a question regarding a relationship between two tables, where in one of them I have an order line, where I have the exact value of the diameter, width, quantity. In the other I have a packing pattern listing, where I have which packaging uses in a given diameter and width range, example:
Order table (wallet):
Order | Item | diameter | width | quantity |
1 | Item 1 | 700 | 560 | 10 |
2 | Item 2 | 700 | 560 | 15 |
3 | Item 3 | 700 | 560 | 20 |
4 | Item 4 | 700 | 560 | 20 |
5 | Item 5 | 700 | 565 | 10 |
6 | Item 6 | 700 | 580 | 1 |
7 | Item 7 | 700 | 590 | 10 |
8 | Item 8 | 700 | 590 | 5 |
9 | Item 9 | 700 | 600 | 10 |
10 | Item 10 | 700 | 600 | 5 |
Table of packaging formats (InfotecEmb):
diameter Minimum | diameter Maximum | width Minimum | width maximum | Pallet |
611 | 710 | 331 | 370 | D9 |
611 | 710 | 371 | 420 | D10 |
611 | 710 | 421 | 490 | D11 |
611 | 710 | 491 | 570 | D13 |
611 | 710 | 571 | 690 | D8 |
611 | 710 | 691 | 790 | D9 |
471 | 533 | 701 | 790 | B9 |
471 | 533 | 791 | 890 | B10 |
471 | 533 | 891 | 990 | D11 |
That is, I want to bring a Pallet column in the order table, considering that the order specification is between the minimum and maximum according to the packaging table.
I tried to concatenate the tables and the if with the rules but without success:
If ( [wallet(diameter)] > = [Infotec Emb (diameter Minimum )]
and [wallet(diameter)] <= [Infotec Emb (diameter Maximum )]
and [wallet(width)] > = [Infotec Emb (width Minimum ) ))]
and [wallet(width)] <= [Infotec Emb (width Maximum )] , [Infotec Emb (Pallet)]) as [Correct packaging]
Does anyone have experience with data in this minimum and maximum format and could you help me in this case? Thanks in advance!
One solution is,
tab1:
LOAD * INLINE [
Order, Item, diameter, width, quantity
1, Item 1, 700, 560, 10
2, Item 2, 700, 560, 15
3, Item 3, 700, 560, 20
4, Item 4, 700, 560, 20
5, Item 5, 700, 565, 10
6, Item 6, 700, 580, 1
7, Item 7, 700, 590, 10
8, Item 8, 700, 590, 5
9, Item 9, 700, 600, 10
10, Item 10, 700, 600, 5
];
Join(tab1)
LOAD * INLINE [
diameter Minimum, diameter Maximum, width Minimum, width maximum, Pallet
611, 710, 331, 370, D9
611, 710, 371, 420, D10
611, 710, 421, 490, D11
611, 710, 491, 570, D13
611, 710, 571, 690, D8
611, 710, 691, 790, D9
471, 533, 701, 790, B9
471, 533, 791, 890, B10
471, 533, 891, 990, D11
];
tab2:
LOAD *, If([diameter Minimum] <= diameter And [diameter Maximum] >= diameter And
[width Minimum] <= width And [width maximum] >= width, Pallet, 'N') As Pallet_Out
Resident tab1
;
tab3:
NoConcatenate
LOAD *
Resident tab2
Where Pallet_Out <> 'N'
;
Drop Table tab1, tab2;
Olá @LucasIsidoro,
I suggest you to have a look at the IntervalMatch prefix. Here you have an explanation and some examples: https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/LoadData/matching-interv...
JG
One solution is,
tab1:
LOAD * INLINE [
Order, Item, diameter, width, quantity
1, Item 1, 700, 560, 10
2, Item 2, 700, 560, 15
3, Item 3, 700, 560, 20
4, Item 4, 700, 560, 20
5, Item 5, 700, 565, 10
6, Item 6, 700, 580, 1
7, Item 7, 700, 590, 10
8, Item 8, 700, 590, 5
9, Item 9, 700, 600, 10
10, Item 10, 700, 600, 5
];
Join(tab1)
LOAD * INLINE [
diameter Minimum, diameter Maximum, width Minimum, width maximum, Pallet
611, 710, 331, 370, D9
611, 710, 371, 420, D10
611, 710, 421, 490, D11
611, 710, 491, 570, D13
611, 710, 571, 690, D8
611, 710, 691, 790, D9
471, 533, 701, 790, B9
471, 533, 791, 890, B10
471, 533, 891, 990, D11
];
tab2:
LOAD *, If([diameter Minimum] <= diameter And [diameter Maximum] >= diameter And
[width Minimum] <= width And [width maximum] >= width, Pallet, 'N') As Pallet_Out
Resident tab1
;
tab3:
NoConcatenate
LOAD *
Resident tab2
Where Pallet_Out <> 'N'
;
Drop Table tab1, tab2;
Output:
It was exactly this exit that I expected !! Thanks a lot for the help! Doubt resolved.