Families with sum of ages of a couple over 70 years old
A certain company plans to provide affordable housing benefits to married employees within the company. This benefit is only available to families where both spouses are within the company, and one of the conditions is that the age of the couple is 70 years old or above. Here is the employee information table of the company:
Employees：
The following is the employee relationship table EmpRel, which records the relationship between two employees. A Relationship of Spouse indicates that they are husband and wife. The fields for Employee 1 and Employee 2 both contain employee IDs, and the relationship in this table is not duplicated:
Try to identify families with total age of 70 and above.
To solve this problem, simply replace the employee fields in the relationship table with the corresponding employee records, and then directly extract the corresponding employee's date of birth. Next, calculating age, age sum, and filtering operations will be very easy.
| A | |
|---|---|
| 1 | =T("Employees.txt").keys(ID) |
| 2 | =T("EmpRel.txt").select(Relationship=="Spouse") |
| 3 | >A2.switch(Emp1,A1;Emp2,A1) |
| 4 | =A2.new(Emp1.Name:Emp1,Emp2.Name:Emp2, age(Emp1.Birthday)+age(Emp2.Birthday):TotalAge) |
| 5 | =A4.select(TotalAge>=70) |
https://try.esproc.com/splx?2EI
A1 reads out the employee information table, and A2 reads out the employee groups with spouse relationships from the relationship table.
A3 uses the switch function to replace the employee ID in the relationship table with the corresponding employee record. This way, the employee's date of birth can be obtained to calculate their age.
A4 uses the table in A2 to generate a new table sequence for calculations, including the names and sum of ages of the spouses:
A5 selects families with total age of 70 and above:
