Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
😊
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 " ?
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;
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 " ?