App Development

cancel
Showing results for
Did you mean:
Contributor II

Join Between IN Qlik Sense

Hello .

I have a Table with Customer data

and Table with Max Min  years   for  Age Category

Customers:
CustomerID, First_Name, Last_Name, Age
1, Abi, Nabi, 27,
14, Wallter, Botler, 18,
28, Bob, Rob, 43,
125, Alice, Talice, 12,

];

Age_Categories:
Age_Min , Age_Max, Age_Category_Name,
0, 2, Infant,
3, 6, L_Child,
7, 12, M_Child,
12, 15, Tenager,
16, 20, Yong1,
21, 27, Young2,
28, 35, Young3,
68, 78, Pensioner,
78, 199, Elder,
];

I need to Bring Age_Category_Name to  each Customer based  on age .

😊

Labels (3)

• left join

1 Solution

Accepted Solutions
Contributor II
Author

Do i  understand correctly  there no simple way

like in SQL :

---- ****----

"Left Join

on Age between Age_Min and Age_Max  " ?

2 Replies
Creator

Customers:
CustomerID, First_Name, Last_Name, Age
1, Abi, Nabi, 27
14, Wallter, Botler, 18
28, Bob, Rob, 43
125, Alice, Talice, 12

];

Age_Categories:
Age_Min , Age_Max, Age_Category_Name,
0, 2, Infant
3, 6, L_Child
7, 12, M_Child
12, 15, Tenager
16, 20, Yong1
21, 27, Young2
28, 35, Young3
68, 78, Pensioner
78, 199, Elder
];

INTERVALMATCH(Age)
LEFT JOIN (Customers)
Age_Min,
Age_Max
RESIDENT Age_Categories;

You need to do an Intervalmatch. One thing is that you have 12 for M_Child and Teenager which will result in a duplicate row because customer "Alice Talice" is 12 years old and falls in both categories.

Below script is meant to take care that synthetic key that is created when intervalmatching:

Customers:
CustomerID, First_Name, Last_Name, Age
1, Abi, Nabi, 27
14, Wallter, Botler, 18
28, Bob, Rob, 43
125, Alice, Talice, 12

];

Age_Categories:
Age_Min & '|' & Age_Max as Age.#key;

Age_Min , Age_Max, Age_Category_Name,
0, 2, Infant
3, 6, L_Child
7, 12, M_Child
12, 15, Tenager
16, 20, Yong1
21, 27, Young2
28, 35, Young3
68, 78, Pensioner
78, 199, Elder
];

INTERVALMATCH(Age)
LEFT JOIN (Customers)
Age_Min,
Age_Max
RESIDENT Age_Categories;

Customers2:
Age_Min & '|' & Age_Max as Age.#key
RESIDENT Customers;

DROP TABLE Customers;
DROP FIELDS Age_Min,Age_Max FROM Customers2;
RENAME TABLE Customers2 TO Customers;

Contributor II
Author