topic Re: Relationship between tables - minimum / maximum in QlikView

Relationship between tables - minimum / maximum

LucasIsidoro — Thu, 14 Jan 2021 18:31:56 GMT

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!

Re: Relationship between tables - minimum / maximum

JuanGerardo — Thu, 14 Jan 2021 18:55:10 GMT

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-intervals-to-discrete-data.htm

Re: Relationship between tables - minimum / maximum

Saravanan_Desingh — Thu, 14 Jan 2021 19:08:50 GMT

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;

Re: Relationship between tables - minimum / maximum

Saravanan_Desingh — Thu, 14 Jan 2021 19:09:27 GMT

Output:

Re: Relationship between tables - minimum / maximum

LucasIsidoro — Fri, 15 Jan 2021 11:25:48 GMT

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