Mimic VLOOKUP based on Date Ranges

Reply
Highlighted
Yellow Belt

Mimic VLOOKUP based on Date Ranges

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:

 

idDateRevenueGross Profit
6132310/31/2016632.1243
613231/10/2017608.76554.72
613235/23/2017632.1243
613236/15/2017-23.4-213.66
613239/20/20171223.1730.89
6132310/11/2017632.1243
6132311/19/2017632.1243
613234/25/2018632.1232.8
6132311/15/2018632.1212.7
613235/23/2019632.1212.7
6132311/20/20191433.1488.7
613233/23/2020737.1317.7

 

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

idNew Sales RepDate
61323Megan5/17/2016
61323 2/19/2018
61323Megan3/12/2018
61323 3/14/2018
61323Eric9/24/2018
61323 10/23/2018
61323Megan11/19/2018
61323 12/4/2018
61323Paras8/27/2019
61323 4/8/2020
61323Megan6/1/2020

 

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

idDateRevenueGross ProfitAttributed To
6132310/31/2016632.1243Megan
613231/10/2017608.76554.72Megan
613235/23/2017632.1243Megan
613236/15/2017-23.4-213.66Megan
613239/20/20171223.1730.89Megan
6132310/11/2017632.1243Megan
6132311/19/2017632.1243Megan
613234/25/2018632.1232.8 
6132311/15/2018632.1212.7 
613235/23/2019632.1212.7 
6132311/20/20191433.1488.7Paras
613233/23/2020737.1317.7Paras

 

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


Accepted Solutions
Highlighted
Major Red Belt

Re: Mimic VLOOKUP based on Date Ranges

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


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

I'll show you how to mimic #VLOOKUP from #Excel in Domo using #MySQL Window Functions. You can use this technique to create RANK, LEAD, and LAG functions as ...

All Replies
Highlighted
Major Red Belt

Re: Mimic VLOOKUP based on Date Ranges

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


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

I'll show you how to mimic #VLOOKUP from #Excel in Domo using #MySQL Window Functions. You can use this technique to create RANK, LEAD, and LAG functions as ...
Highlighted
Yellow Belt

Re: Mimic VLOOKUP based on Date Ranges

Perfect! Thank you!

Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.