-
"Documentation Mode" in Magic ETL/SQL
A toggle next to "Try the new Magic ETL" would read "Documentation Mode" and would: Put an opaque film over the whole Magic ETL map Disallow changes to the tiles Enable the ability to draw and color-code sections of the ETL Be able to notate what each section is doing An additional item in the Save menu would be "Save and…
-
Compare MAX date with YOY: Month-to-Date (MTD) Comparison
Trying to create a sql statement in magic ETL to compare YOY: Month-to-Date (MTD) by MAX date. My current code from is: CASE WHEN (MONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE) = MONTH(CURDATE()) and YEAR(MEMBERSHIP_INSTANCE_PURCHASE_DATE) = YEAR(CURDATE()) and DAYOFMONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE)…
-
What is the problem with my beastmode?
I am trying to make an outlier flag that flags values 50% above of 50% below the budgeted, or daily goal mount. I made two threshold calculations(dailygoal / 2) & (dailygoal * 1.5). I keep getting an error message, but I am not sure what I am doing wrong. Any suggestions? Thanks, and my sql command is attached below. CASE…
-
UTC to PST conversion in ETL
Hi, I am trying to convert the "Date" from UTC to PST in the ETL. Currently, I created "Send Date PST" with the formula below. TIMESTAMP((convert_tz(`SendDate`, 'UTC', 'America/Los_Angeles'))) However, when I try to use it in the card and filter for the last 4 hours, my card is blank: It works when I do the same thing with…
-
lookup function in beastmode?
Hi all, I have a table that has these columns: loan number other loan number first principle balance hi type description 2nd mortgage amount is the field we are trying to create The logic is such: when hi type description = '1st mortgage' and other loan number is > 1 then i want to look up the first principle balance of…
-
Can I run a Stored Procedure to an external MS SQL and import resulting dataset?
I have large amounts of data stored in an external MS SQL database. Complicated queries are run via a stored procedure, and the resulting dataset is written to a table that I currently have connected to DOMO to produce client dashboards. I'd like to call the stored procedure from the DOMO data connection, have it run,…
-
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…
-
Disable Views SQL Editor when it's been enabled
The SQL Editor in Views has proven to be invaluable when trying to fix other people's broken views. However, once the underlying SQL has been fixed I would like the ability to switch the view back to it's normal look and feel: most of my users don't know SQL and get freaked out when they see that big black box with the…
-
Help with SQL query
My first post so apologies if I have dropped this in the wrong forum. I have a slightly more complex dataflow than Magic ETL will handle and I am hoping I can manage this with SQL, however my SQL is a little rusty (read metal falling apart). The requirement is possibly quite common in that I need to measure how many…
-
MAGIC ETL/DATAFLOWS Q&A from Domopalooza
The product team enjoyed answering questions from the audience during the final session at Domopalooza. Below you will find the answers to all questions related to Magic ETL and Dataflows (including those that weren't mentioned on stage). Q: When are you going to upgrade the MySQL version in MySQL DataFlows? A: We do not…
-
Using LAG() OVER function to get difference in a Snapshot dataset
Hello! I am trying to get the right formula to calculate the difference in data within a table (same data APPENDED to create snapshots). Here is an example of a beast mode I have been playing with. I have unique data by Opportunity ID & Snapshot YearWeek in the rows summarized in this chart, I would like to take the 202413…
-
Different time zone
Hi! I'm trying to configure the time zone in ETL but my query is resulting in different times when the function calculates the difference, for the same column. ETL time zone function: Imported table from SQL Server: Result after time zone changes: The date range of the table is from Feb 24 to Mar 12. From Feb 24 to Mar 8…
-
Unable to Create Same Time vs Yr Ago
I'm trying to create a dynamic Comparison Value that adjusts the view based on Time Period Selection. I was able to create a Gauge Value that works, but am struggle with the Beast Mode that would allow the end user to view vs same time year ago value. I have the code below and have tried various adjustments, but keep…
-
Is it possible to batch edit source data?
I have a large dataset that needs some cleaning. We have appended to this dataset daily over several years and some of the data values have drifted over time as users have renamed what are essentially labels for different entities. If this were SQL, I could write an update statement and quickly replace the values in label…
-
Domo workbench - "attempting to open data source"
When a workbench job is executing, does anyone know what is happening when the preview window execution step says "attempting to open data source"? We will have a job that gets stuck on this step for several hours and it is unable to get past this step, then the query times out. The workbench job is executing a stored…
-
How do I get started with MySQL Dataflows?
Hello all, I have roughly two years experience with creating Magic ETLs, but no experience with MySQL dataflows. I'm seeking to experiment with MySQL just for the sake of experimentation. Background: I've written thousands of Case When Statements for Beast Modes and calculated fields, but have very little actual SQL…
-
Can use FIXED with SQL data
-
Aggregating Data for a Beastmode - Counting Survival Analysis
Hi there -- I have a dataset that has an individual record per employee that states if they are active or terminated and that gives their current tenure or the tenure at the time that they left the company. I'm trying to do a survival beastmode using a bar chart where I show 100% at tenure = 0, and then at tenure = 1, I…
-
30 Day Interval
Hello! I am looking to create a beastmode that sums video views and revenue separately in the 30-days after the asset is published. While the beastmode is validating and looks to be calculating accurately, the QA is very off. In the screenshot example, I manually filtered the time frame for 30-days after the published date…
-
SQL Query against dataset
Hi, I am wondering if anyone has ever had a ask for a way to just write sql against a dataset, or join to datasets. I know we have redshift, but this would be more like a SQL IDE you would use in a relational database. I know its kinda an odd question, but we have some users who prefer sql vs magic etl. I have seen the AI…
-
How to disable SQL Editor on a View once it's been enabled?
I enabled the new SQL Editor on one of my views this morning to troubleshoot an issue: it's a wonderful tool, and helped me solve a very bizarre problem. Unfortunately, the editor takes up a lot of screen real estate, so I would like to disable it and return to the normal form of the view now that it's no longer needed.…
-
How to count number of Occurrences in a Column
I have a dataset where I am trying to get a count of each occurrence a value in a specific column has: For the example above I would want the result to return that there are Two : "0's", Three: "Nulls", and Three: "1's".
-
Creating a new column using Formula in Magic ETL
Hello, I have a dataframe with a column named 'Screening Date' in that column are dates and null values. I want to create a new column called 'Completed' that looks through 'Screening date' and if there is a date in a particular row I want to Write "Yes" in my new column. If the 'Screening date' column is null I want to…
-
SEC_TO_TIME Not Displaying in HH:MM:SS
Hello! I am attempting to use the sec_to_time to convert my duration in seconds to HH:MM:SS although, I can not sum duration as it duplicates and when I apply just SEC_TO_TIME(`duration_seconds`) the calculation displays as 'count' and does not display as HH:MM:SS. When I do (SEC_TO_TIME(SUM(`duration_seconds`) it…
-
Not sure why I am getting a SQL Error
I have the columns correctly added, but even though its there in the columns list, I am getting an error. PLease check the screenshot to view details
-
Creating a Rolling 4Q Total
Hello, I have a dataset with a list of all customers and their recurring/non-recurring recognized revenue by quarter. Our business has a tremendous amount of seasonlity so I'm trying to report a rolling 4q total. How could I go about showing the sum of a quarter and the 3 trailing as well?
-
Duplicate value handling in ETL
I have a column where there are around 34 values that are duplicated for one line that is supposed to be for two people. See picture for example. Ideally it'd be great to have one formula that can distinguish where the break would be so I don't have to manually create 34 lines of code, and have to continually add lines…
-
MS SQL Server Connector Error
We have a job that extracts data from SQL through the SQL Server connector. It has been running successfully for a long time but it started failing yesterday. I tried recreating the data connection and let the query only pull data for a few days. It ran successfully. However, after I update the query to pull more data…
-
Problems with Workbench, ODBC, MySQL and the data specialist exam
Hi I have a problem with taking this exam: https://learndomo.domo.com/learn/course/383/play/1608/data-specialist-practical-test-2h The task wants you to use Workbench 5 to set up a MySQL job and then import the data from Workbench to Domo. The connection to Domo from Workbench works fine. When I use the parameters for the…
-
DateDiff formula in Redshift
Hi Team, The formula below was built as a beastmode but would like to move it to dimensions, the backend. However, "DATEDIFF()" isn't recognized in Redshift, any workarounds? Thanks CASE when ("date","published_date") >0 and DATEDIFF("date","published_date") <=29 then '01 Month' when DATEDIFF("date","published_date") >29…