-
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…
-
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…
-
"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…
-
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…
-
replace a Dataset within SQL workflow and within transforms
I think could be great to be able to replace a Dataset in a SQL workflow with a new one, not only adding the new data set but as well replacing Dataset name within all transforms. Maybe adding an option to Edit, Delete (image 1) (Replacing if dataset name on sql statement is "name"). Let's say, I have a new data set (using…
-
Is there a way to bucket columns similar to excel in DOMO?
I am trying to have a pivot table that shows weekly plans vs actuals. Is there a way to add a top layer of that to group the value columns into the top three pillars? Supply, demand, revenue. Thanks
-
Change SQL Server Connector to import datetime data with greater than 1 second granularity
SQL Server's Time and DateTime fields store data to the millisecond (Time datatypes are accurate to 100 nanoseconds). However, the connectors truncate those data types to the nearest second. I put in a ticket when I found this — I really do need to keep the milliseconds when importing — but was told it's working as…
-
Brick with Ten Datasets Question
Hey all, In the template for the "Brick with Ten Datasets", is there more specific documentation on the queries used to pull summary info into the card? For example, the https://developer.domo.com/docs/dev-studio-guides/data-queries link is provided as documentation for the code snippet below but there isn't even a…
-
SQL formula not working?
I have a series of tests that run to determine a pass or fail. All the formulas work but this one is giving me problems - CASE WHEN Critical/Major= 'Common' AND
No>= 2 AND Critical/Major= 'Major' AND No>= 1 THEN 'Fail' Any one know why this would not be returning a Fail?
-
Need to extract all queries used in our SQL Server connectors.
We recently realized that someone in the past hard-coded database names — names which have since changed — in some of the SQL queries used in the SQL Server connectors. Unfortunately, we have hundreds of connectors, and opening up each of them manually to look for a problem will not be fun. To avoid doing that I want a way…
-
Beast Mode - Checking data from a different row to generate result
I'd like to create a Beast Mode that takes a value and checks it against another row in my table. I have a bunch of human annotation data. I want to look at a content_id and then flag if that content_id has been in the QC job. I've made a rough sketch of how the data looks (apologies for the formatting. This is my first…