How do I use window function date ranges in DOMO ETLs?
The short version, I need the following query to work somewhere in DOMO ETL:
AVG(`Score`) OVER (PARTITION BY Company
ORDER BY 'Date` RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND INTERVAL '0' DAY FOLLOWING)
More detail:
I tried using group by but cannot use window functions, formulas but can't use aggregations, and rank and window but is only limited to preceding rows (which would be fine if I fill in the dates for each company using the connector, but I'd like to keep it simple).
Is there a beta function to fully allow ETL window functions, or am I missing something? I can probably accomplish this in a beast mode (haven't tried), but this is only a small piece of many other connected ETLs. This one is no different and feeds into another ETL to produce a total score or transposes for various graphs. I'd rather not have to make multiple beast modes and redo the structure of this particular project just for one piece to work.
I could use Python pandas SQL but I would like to avoid using the scripting tiles if possible
PS - I have no idea why Company happened to code inline correctly compared to Date and Score. Pointing it out as an interesting curiosity in case that piques the interest of any DOMO folks.
Best Answer
-
Regrettably, window functions aren't supported in Magic ETL formulas, and as you've already determined it's not possible to express that window frame using the Rank & Window tile. Including the frame boundaries in the original data, using Beast Mode, or using the Python Script tile are all options.
If the whole input dataset is reasonably small (say, less than a million rows) you could probably do it with a cross product join of the table against itself:
EDIT: There's a better way to do this than I had originally described. Rather than a full cross product, cross products within each Company result in less intermediate rows and obviate the need for the company equality check later.
1. Make sure you have a row key of some kind; that is, one or more columns that can together uniquely identify a row.2. Join the data to itself using
Company
as the join column. Since you can't connect one tile to another "doubly", send one connection to a Select tile first and select just the Score, Company, and Date columns. Give them new names as well so they don't collide; prefix all of them withOther_
4. Add Formula next, with a column called
in_frame
using this expression:CASE WHEN DATEDIFF(Date, Other_Date) < 91 THEN 1 ELSE 0 END
5. Now a Group By, using the "row key" column(s) that we mentioned we'd need in step 1 as the grouping key. You'll need to add an aggregate for every non-grouping column that you want to preserve in the output as well; this can just be "First value". For the window calculation, add the aggregate expression:
SUM(CASE WHEN in_frame THEN Score ELSE 0 END) / SUM(in_frame)
Randall Oveson <randall.oveson@domo.com>
2
Answers
-
Regrettably, window functions aren't supported in Magic ETL formulas, and as you've already determined it's not possible to express that window frame using the Rank & Window tile. Including the frame boundaries in the original data, using Beast Mode, or using the Python Script tile are all options.
If the whole input dataset is reasonably small (say, less than a million rows) you could probably do it with a cross product join of the table against itself:
EDIT: There's a better way to do this than I had originally described. Rather than a full cross product, cross products within each Company result in less intermediate rows and obviate the need for the company equality check later.
1. Make sure you have a row key of some kind; that is, one or more columns that can together uniquely identify a row.2. Join the data to itself using
Company
as the join column. Since you can't connect one tile to another "doubly", send one connection to a Select tile first and select just the Score, Company, and Date columns. Give them new names as well so they don't collide; prefix all of them withOther_
4. Add Formula next, with a column called
in_frame
using this expression:CASE WHEN DATEDIFF(Date, Other_Date) < 91 THEN 1 ELSE 0 END
5. Now a Group By, using the "row key" column(s) that we mentioned we'd need in step 1 as the grouping key. You'll need to add an aggregate for every non-grouping column that you want to preserve in the output as well; this can just be "First value". For the window calculation, add the aggregate expression:
SUM(CASE WHEN in_frame THEN Score ELSE 0 END) / SUM(in_frame)
Randall Oveson <randall.oveson@domo.com>
2
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