Mimic VLOOKUP based on Date Ranges

JamesCH

I'm looking for a way to merge two sets of data based on approximate matches. I have two datasets: one with all of the daily sales data for a customer based on their id number:

 

id	Date	Revenue	Gross Profit
61323	10/31/2016	632.1	243
61323	1/10/2017	608.76	554.72
61323	5/23/2017	632.1	243
61323	6/15/2017	-23.4	-213.66
61323	9/20/2017	1223.1	730.89
61323	10/11/2017	632.1	243
61323	11/19/2017	632.1	243
61323	4/25/2018	632.1	232.8
61323	11/15/2018	632.1	212.7
61323	5/23/2019	632.1	212.7
61323	11/20/2019	1433.1	488.7
61323	3/23/2020	737.1	317.7

 

And another table that shows the history of who managed that customer:

id	New Sales Rep	Date
61323	Megan	5/17/2016
61323		2/19/2018
61323	Megan	3/12/2018
61323		3/14/2018
61323	Eric	9/24/2018
61323		10/23/2018
61323	Megan	11/19/2018
61323		12/4/2018
61323	Paras	8/27/2019
61323		4/8/2020
61323	Megan	6/1/2020

 

In Excel, I could use lookup functions with approximate matches to see who the sales should be attributed to:

id	Date	Revenue	Gross Profit	Attributed To
61323	10/31/2016	632.1	243	Megan
61323	1/10/2017	608.76	554.72	Megan
61323	5/23/2017	632.1	243	Megan
61323	6/15/2017	-23.4	-213.66	Megan
61323	9/20/2017	1223.1	730.89	Megan
61323	10/11/2017	632.1	243	Megan
61323	11/19/2017	632.1	243	Megan
61323	4/25/2018	632.1	232.8
61323	11/15/2018	632.1	212.7
61323	5/23/2019	632.1	212.7
61323	11/20/2019	1433.1	488.7	Paras
61323	3/23/2020	737.1	317.7	Paras

 

But I'm not sure how I would be able to do something similar with DOMO. Would appreciate the assist!

jaeW_at_Onyx

Youtube Tutorial:  https://youtu.be/s57DSVmGwQQ

 

I think I understand what you're looking for, but to find an equivalent, I think I would try to frame the question in simple English in terms of defining the behavior that you want.  

 

Does Table B define 'the rep who manged an account on XYZ date'?

If so then you could define a table that states the start_date and end_date that a rep owned the account.

i.e.

Megan owned the account between 5/17 and 9/23

Eric owned the account between 9/24 and 11/18.

 

If that's true, then define the rules for how you want to construct your lookup table.

'Keep all rows where the rep name is not null.

End Date is the Start Date of the next row minus one day (this can be achieved with the LEAD() function).

 

With your Lookup table defined, you can now JOIN to your transaction table.

 

SELECT 

...

FROM

transaction a

JOIN

rep_lookup b

ON

 

Hope that helps!

a.date between b.startDate and b.endDate

jaeW_at_Onyx

Youtube Tutorial:  https://youtu.be/s57DSVmGwQQ

 

I think I understand what you're looking for, but to find an equivalent, I think I would try to frame the question in simple English in terms of defining the behavior that you want.  

 

Does Table B define 'the rep who manged an account on XYZ date'?

If so then you could define a table that states the start_date and end_date that a rep owned the account.

i.e.

Megan owned the account between 5/17 and 9/23

Eric owned the account between 9/24 and 11/18.

 

If that's true, then define the rules for how you want to construct your lookup table.

'Keep all rows where the rep name is not null.

End Date is the Start Date of the next row minus one day (this can be achieved with the LEAD() function).

 

With your Lookup table defined, you can now JOIN to your transaction table.

 

SELECT 

...

FROM

transaction a

JOIN

rep_lookup b

ON

 

Hope that helps!

a.date between b.startDate and b.endDate

JamesCH

Perfect! Thank you!