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:
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!
Best Answer
-
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 ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
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 ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Perfect! Thank you!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive