How to do generate_series in a SQL dataflow
Hi,
I'm trying to elimate the need for a database between our production system and Domo by doing all ETL work within Domo (I know this may take longer to process). The database is PostgreSQL and some of the queries that I need to do in Domo use a function called generate_series, but it appears there is not a simple MySQL equivalent to this. I'm trying to do a SQL dataflow for the ETL, but need help figuring out how to code that function to work in Domo. Here's one of the simpler examples where this function is being used:
SELECT id, owner, date_trunc('MONTH',day)::DATE AS date, SUM(amount/days)AS splitamount
FROM(SELECT*,end_date-start_date+1 AS days,generate_series(start_date, end_date, INTERVAL '1 day')::DATE AS DAY
FROM opportunity) k
WHERE NOT EXISTS(SELECT oppty_id
FROM orders t
WHERE t.oppty_id=k.id)
GROUP BY 1,2,3;
Can anyone help advise how to make the same thing happen in MySQL?
Best Answer
-
Domo has a connector called Domo Dimensions that is a data table of all calendar dates that is useful for this type of work. If you search for calendar in the connectors section of the App Store, it should show up. If it doesn't ask your CSM to enable it. This can save you some work when needing to build a table full of dates.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
-
The trouble with the first possibility is that I need to have a specific end date as well as a start date. This serires is used towards calculating when and how long a campaign ran to understand the revenue generated from it.
I'll look into the Domo Dimensions as that sounds like it might be a good option and help with the other SQL statements I need to work out for this too.
0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有