-
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…
-
Beast Mode is valid but does not print all options, how can I fix it?
Created the beastmode below and although its valid I'm only seeing options "Low Performers" and "Other". The beastmode is suppose to look at revenue and at month-12 revenue and meet certain qualifications to identify it as either Non Public Videos, Low Performers or Videos Exceeding Lifespan. Thank you in advance! i.e. if…
-
Creating a Beastmode that meets Revenue Requirements
Hi! We're looking to build a beastmode that meets two requirements to then print a result. For example if a video generated more than $1K in total revenue and more than $100 during month-12, then "repost". See current beastmode below: Background: The dataset tracks the lifespan of revenue per video title, in a 12-month…
-
How to reference a Beastmode in another Beastmode?
For example we have beastmode-A that tracks monthly revenue for 12 months, and I want to reference this beastmode-A in beastmode-B which will say "Month 12 > 100 then XYZ". Any ideas are greatly appreciated! Thank you!
-
MySQL
For this question: Join together two DataSets with MySQL using the name, year, and gender columns and include appropriate indexing. Not sure what to index here, is there any additional resources around indexing? The course doesn't go over it explicitly and this link doesn't either:…
-
LEAD IGNORE NULL in Magic ETL?
Hi there, I am trying to rewrite a Redshift Dataflow into Magic ETL. I ran across a specific function that was available in Redshift that I dont think is possible in Magic ETL. I wanted to get the community's input and advice to see what you guys think. This is the function found in Redshift: LEAD(CASE WHEN t2."channel"…
-
Can you use Magic ETL to 'explode' table of transactions to all days?
Table A has transactions that show an item's "Old Status" and "New Status" along with the date of the transaction. For example, row 1 shows that item XYZ changed from "Regular Price" to "Promo Price" on 1/1/2023. Row 2 shows that item XYZ changed from "Promo Price" to "Regular Price" on 1/4/2023. Row 3 shows that item XYZ…
-
Trailing Totals Deep Dive
Yesterdays Community Meeting was awesome with lots of valuable insights, but was wondering if someone could elaborate a bit more on how the trend SQL works, I know we saw 2 of the transforms but I'm having a hard time figuring out the rest, like how is information being filled so that the rows 91-1 represent consecutive…
-
Ability to save and easily reuse commonly used strings of logic within MagicETL/SQL
It would be really nice, when creating or editing a Magic ETL, to be able to easily reuse sequences of logic that could be easily adapted to any environment. Recursive append loops Rank and filter Split column and dynamic pivot The option to select which saved item you want to use would appear in the Magic ETL edit menu on…
-
Is there a way to duplicate a value one row down?
I have a column with a numeric value and null value every other cell. I want to replace the null cells with the numeric value that is directly above it. Thanks in advance!
-
Combine tables using ETL inner join
Hi everyone, I'm trying to combine two federated tables using the inner join in Magic ETL. Both tables have over 9.5M rows but when I join them, the result is only 87K. I tested the join in Snowflake and it resulted in 9.5M by the logic: SELECT * FROM 'A' INNER JOIN 'B' ON A.X = B.X What could i be doing wrong? *I'll need…
-
IF Statement Inside Beast Modes
Currently, you can use the IF statement inside of DataFlows (both MySQL and Magic ETL v2). However, this function is not available in Beast Modes. Most CASE statements I write are small and simple enough (they only have two outputs) that I could write them in an IF statement. IF statements are simpler to write and take up…
-
Append Output dataset help
Hello all, I have an Magic ETL (A) that with a output dataset - say OUTPUT_1. What I am looking for, is when every time I run the ETL, I need the output results to be appended. At the same time it should not create duplicate records. I created a new Magic ETL (B) with this OUTPUT_1 as input dataset and outputted to…
-
Question on Domo SQL syntax
Hello all, I am trying to do Magic Transform using SQL option. I have embedded a SQL query but it is erring as it is not compatible with Domo syntax. Appreciate your help in this regard. DATE_PART('WEEK',CAST(TO_CHAR(TO_DATE(TO_CHAR(PRODUCT_KEY),'YYYYMMDD'),'YYYY-MM-DD') AS DATE)) AS "SaleWeek",…
-
Aggregate and ETL question
Hello all, I have the below workflow in my Magic ETL. Just posting a sample version below. My question is - I have a ProductID that I join from both the data sources and then do a group by and output to a file. But I also need to know Total count of Product ID or row count from TEST spreadsheet source. Is there a way I…
-
Domo API to MS-SQL
I tried unsuccessfully to find an example of pulling a Domo dataset directly to MS-SQL via the API. So I created my own. I am not a python coder but my script works just fine. Does anyone have a version of this subroutine that does not require defining the column names (ie header)? #Retrieve dataset from Domo API def…
-
Sum column when i have duplicates in other two columns?
I have these columns and I would like to sum the cost column only when the Publication and Year column have duplicates, so I can see the cost per Publication per year. Brain fart, I am assuming this is pretty straight forward? I'd like to do it in ETL if possible. Is there some SQL I can utilize to do this in the formula…
-
SQL partition password reset
Hi - I put in a help ticket without success so far. We are using a SQL partitions connector to bring in accounting data. Following an apparent password reset on database access, some of the datasets continued to work while some larger datasets stopped working but continued to bring in blank lines of data. I reset my…
-
Can I get the MIN date over a partition with a formula tile?
I need to walk a date column partitioned by the value of another column (UID) to find the earliest date for that group. I do not want to collapse the rows. I've tried a number of approaches to this with a SQL perspective, but I'm starting to think Magic ETL wants me to use a Group by. Here are a couple of examples of my…