topic Re: Join Between IN Qlik Sense in App Development

Join Between IN Qlik Sense

ilyakoch — Sat, 16 Nov 2024 01:39:45 GMT

Hello .

I have a Table with Customer data

and Table with Max Min years for Age Category

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

];

Age_Categories:
Load * Inline [
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,
36, 42, Adult1,
43, 55, Adult2,
56, 67, Adult3,
68, 78, Pensioner,
78, 199, Elder,
];

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

Thank you In Advance.

😊

Re: Join Between IN Qlik Sense

RsQK — Mon, 17 Aug 2020 07:55:02 GMT

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

];

Age_Categories:
Load * Inline [
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
36, 42, Adult1
43, 55, Adult2
56, 67, Adult3
68, 78, Pensioner
78, 199, Elder
];

INTERVALMATCH(Age)
LEFT JOIN (Customers)
LOAD
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:
Load * Inline [
CustomerID, First_Name, Last_Name, Age
1, Abi, Nabi, 27
14, Wallter, Botler, 18
28, Bob, Rob, 43
125, Alice, Talice, 12

];

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

Load * Inline [
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
36, 42, Adult1
43, 55, Adult2
56, 67, Adult3
68, 78, Pensioner
78, 199, Elder
];

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

Customers2:
LOAD *,
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;

Re: Join Between IN Qlik Sense

ilyakoch — Mon, 17 Aug 2020 08:50:27 GMT

Thank You for your reply .

I understand your answer for the problem.

Do i understand correctly there no simple way

like in SQL :

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

"Left Join

on Age between Age_Min and Age_Max " ?