Apologies if this has been covered already. I searched and was unable to find a solution. I have two tables, one has Sale Transaction Date, Account Number and Sale Transaction Amount. The other table has the Account Number, Account Rep Name, Account Rep Begin Date and Account Rep End Date (dates are the periods that the Rep covered the account). Is there a way to Join Account Rep Name to the Sales Table using the Transaction Date and Account Number?
Sales Table
Sale Transaction Date | Account Number | Sale Transaction Amount |
1/15/2019 | 1234 | 10 |
7/15/2019 | 1234 | 50 |
3/15/2020 | 1234 | 20 |
5/10/2020 | 1234 | 200 |
1/15/2019 | 5678 | 10 |
7/15/2019 | 5678 | 50 |
3/15/2020 | 5678 | 20 |
5/10/2020 | 5678 | 200 |
Account Rep Assignment Table
Account Number | Account Rep Name | Account Rep Begin Date | Account Rep End Date |
1234 | John D | 1/1/2019 | 6/30/2019 |
1234 | Amy C | 6/30/2019 | 12/31/2019 |
1234 | Jane D | 1/1/2020 | 4/15/2020 |
1234 | Bret T | 4/16/2020 | |
5678 | Alex D | 1/1/2019 | 6/30/2019 |
5678 | Paul C | 6/30/2019 | 12/31/2019 |
5678 | Alex D | 1/1/2020 | 4/15/2020 |
5678 | Alex D | 4/16/2020 |
Desired output
Sale Transaction Date | Account Number | Sale Transaction Amount | Account Rep Name |
1/15/2019 | 1234 | 10 | John D |
7/15/2019 | 1234 | 50 | Amy C |
3/15/2020 | 1234 | 20 | Jane D |
5/10/2020 | 1234 | 200 | Bret T |
1/15/2019 | 5678 | 10 | Alex D |
7/15/2019 | 5678 | 50 | Paul C |
3/15/2020 | 5678 | 20 | Alex D |
5/10/2020 | 5678 | 200 | Alex D |
Solved! Go to Solution.
Hi @nickbecks ,
You can use the join + a filter tool to get that same result.
Example attached.
Best,
Fernando Vizcaino
Hi @nickbecks, I suggest joining the datasets on Account Number and then applying a Filter tool downstream to get your appropriate universe. Attached is an example to showcase my suggested approach.
Hope this helps.
Thanks for this solution. As a followup to this, if the Account Rep Assignment Table only has Account Rep Begin Date, is there a way to calculate the End Date by using the Account Rep Begin Date from the preceding record for that same Account Number?
Account Rep Assignment Table
Account Number | Account Rep Name | Account Rep Begin Date | |
1234 | John D | 1/1/2019 | |
1234 | Amy C | 6/30/2019 | |
1234 | Jane D | 1/1/2020 | |
1234 | Bret T | 4/16/2020 | |
5678 | Alex D | 1/1/2019 | |
5678 | Paul C | 6/30/2019 | |
5678 | Alex D | 1/1/2020 | |
5678 | Alex D | 4/16/2020 |
Hi @nickbecks,
Yes, you can use the Multi-Row formula tool to compute the end date. The formula would look something like:
IIF(
ISNULL([Row+1:AccRepBeginDt]),
DateTimeToday(),
[Row+1:AccRepBeginDt]
)
Attached is an implementation of the solution as well.