Relationship between tables - minimum / maximum

[LucasIsidoro]

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!

[JuanGerardo]

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

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Output:

[LucasIsidoro]

It was exactly this exit that I expected !! Thanks a lot for the help! Doubt resolved.