Dynamic join

[davidogundipe]

I have two files. I want to get the maximum payment amount of an individual using the join tool. We'll call the two files data1 (excel spreadsheet) and data2 (excel workbook). Data1 has three fields: name, age at retirement, and year of payment (either 2019 or 2020). Data2 has two fields: age and maximum payment. The maximum payment of each age changes between 2019 and 2020, so there are two spreadsheets in the data2 workbook, one for 2019 and one for 2020. Is there a way to dynamically switch between the two spreadsheets to retrieve the correct maximum payment based on the year of payment in the data1 spreadsheet? 

Example:

Data1

Name	Age at retirement	Year of Payment
John D.	30	2019
John D.	30	2020
Jane D.	35	2019
Jane D.	35	2020

2019 spreadsheet in Data2 workbook

Age	2019 Max
30	5000
35	6000

2020 spreadsheet in Data2 workbook

Age	2020 Max
30	4000
35	4500

Result

Name	Age at Retirement	Year of Payment	Maximum
John D.	30	2019	5000
John D.	30	2020	4000
Jane D.	35	2019	6000
Jane D.	35	2020	4500

I tried using the dynamic input tool, but I didn't implement it correctly. Please let me know if there's a solution. Thanks!

[AngelosPachis · Accepted solution]

Hi @davidogundipe ,

I have used a dynamic input to solve this issue. Have a look at the following workflow and how the tools are configured, I think it may help

Workflow

Output

Regards,

Angelos

[davidogundipe]

Thanks so much! This worked.