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 to like any users posts that helped you and accept 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.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 338 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有