-
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…
-
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?
-
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…
-
Creating an Artificial downtime for maintenance for only week and work hours
I am trying to calculate downtime within DOMO. The downtime we have currenlty coming from the other system does not put in the factors of weekends and holidays as well as after hours. How can I calculate a correct amount of time between the starttime to endtime while only subtracting within the window. For example if A…
-
Documentation needed for using Parameters in SQL Server Connector
I would like to have our SQL Server Connector import only data that has been altered since the last successful import (something like dtLastUpdated >= _BATCH_LAST_RUN_, but only if that value is only saved on a successful import. In the Details section of the connector there is a Query Parameter option that hints at this…
-
SQL in ETL
Hi All, I'm currently trying to recreate a MySQL flow in Domo to ETL. One of the first things we are doing in a table is taking out extra characters from a column to create a new trade_id Here is how we are doing it. when I go to add the formula tile and copy and paste this syntax, ETL is saying it doesnt recognize the…