Join by Multiple Fields with >= or <= Criteria

[nickbecks]

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

[fmvizcaino · Accepted solution]

Hi @nickbecks ,

You can use the join + a filter tool to get that same result. 

Example attached.

Best,

Fernando Vizcaino

[AbhilashR]

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.

[nickbecks]

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

[AbhilashR]

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.