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 DateAccount NumberSale Transaction Amount1/15/20191234107/15/20191234503/15/20201234205/10/202012342001/15/20195678107/15/20195678503/15/20205678205/10/20205678200 Account Rep Assignment Table Account NumberAccount Rep NameAccount Rep Begin DateAccount Rep End Date1234John D1/1/20196/30/20191234Amy C6/30/201912/31/20191234Jane D1/1/20204/15/20201234Bret T4/16/2020 5678Alex D1/1/20196/30/20195678Paul C6/30/201912/31/20195678Alex D1/1/20204/15/20205678Alex D4/16/2020 Desired output Sale Transaction DateAccount NumberSale Transaction AmountAccount Rep Name1/15/2019123410John D7/15/2019123450Amy C3/15/2020123420Jane D5/10/20201234200Bret T1/15/2019567810Alex D7/15/2019567850Paul C3/15/2020567820Alex D5/10/20205678200Alex D

Join by Multiple Fields with >= or <= Criteria

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

Workflow

Accepted answers

fmvizcaino

Hi @nickbecks ,

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

Example attached.

Best,

Fernando Vizcaino

2020-05-11 Join by Multiple Fields with Criteria.yxmd

All comments

fmvizcaino

Hi @nickbecks ,

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

Example attached.

Best,

Fernando Vizcaino

2020-05-11 Join by Multiple Fields with Criteria.yxmd

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.

sample.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703