-
Issues with SUM Function within SQL Transformations
Hello everyone, I've been encountering issues with the SUM() function within my SQL transformations. I've imported large datasets and added my own calculations (as shown in the attached photos). When I try to query the three mentioned columns and group the results by month, specifically using SUM(average meals...),…
-
Update the Code Block Formatting in the Community Forum
Right now, the code block formatting in the community forum doesn't work well at all. It could really use an upgrade. Below I've listed all the issues I've run across while using it. Also, I understand that identifying exactly which type of code is in the code block can be difficult, leading to many of the issues seen…
-
SQL Connector dataset set to 'Replace' but the data remains the same
I am using the preferred SQL Server connector and pulling in data. However I want to "restart" this dataset and wipe it out to 0 rows. I set the update method to Replace and modified my query to return 0 rows. I can see my preview returns no rows: However when I run this, the updated rows shows - and the total row count…
-
SQL Connector Returning No Rows
I'm sure this has been answered but I can't find it, so I'm hoping this is a simple answer. I have a SQL dataset that was returning rows but currently returns no rows. This is expected and should continue to switch between returning rows and returning no rows. The issue is that when it returns no rows the dataset in DOMO…
-
Joins: Automatic Identification of Relationship Direction
Please add: Automatic identification of relationship direction within a Join tile. An option to amend this relationship direction (where appropriate) within the Join tile. E.G A drop-down with 1-2-1,1-2-Many, Many-2-1, Many-2-Many (inc warning) e.t.c A visible representation of this relationship direction with ERD arrows…
-
Extract multiple sets of numbers from a single string
I am trying to extract two different sets of numbers from a string. I have tried substring and Regexp, but to no avail. Examples of the stringss- Possible string format #1 Company Name - 2024-07-01-2024-07-31 - GPPC - YTUBE - VYT - 63784 - 2782837 - 458573682 - [745-000121-003] Possible string format #2 Company Name -…
-
Beastmode to flag an item if the date is longer than a week from the current date.
Hello, I am trying to write a beastmode to 'FLAG' and item if the submission date is longer than a week from the current date. How would I proceed? See below for my current beastmode that flags something if it does not fall into the current week of the current date. My thought process is if the date is longer than the…
-
Running Into Error with BeastMode and Table
I'm working on a beastmode where I am trying to assign a label to individual orders based on their price_tier and their Product Name. If an order's price_tier is equal to the MAX of the price_tier for a Product Name, I want it labeled "Sold". If an order's price tier is less than the max, I want it labeled "Discount". And…
-
Search the Dataset amongst all the available datasets
Hello, I would like to search the datasets (amongst many datasets) that uses Aggregate functions (Such as count or sum). Please help me with the steps.
-
Help figuring out how to get a data set to work with a funnel
For context the data set image linked is a representation of the data I am working with to protect our real customer data. I do not have the ability to reformat the data coming in, only use magic ETL and SQL transformations to convert this data into the format it needs to be in. I can not find a way to make the funnels…
-
ETL fails due to "This data set does not support data version filtering."
I have my SQL raw dataset set to merge new rows which is working correctly: This SQL dataset is the input for my ETL attempting to process New rows then Upsert in the output dataset: However my ETL fails to run due to the stated error: Am I setting something up wrong? My raw SQL data is not partitioned but it is using the…
-
Seeking Best Practice Advice: Reporting Financials in Domo
Hi community, What is best practice for reporting financials in Domo? Currently, we have raw financials, account by account, fed in through a simple CSV. There are no summaries in this CSV, so I have to aggregate them with Beastmodes to get to summary numbers. For example, EBITDA is calculated as such (see code below) This…
-
Database Management View Instead of Datasets/ETLs view
Currently users view their assets in a list in the Data tab. They have to use naming conventions, or tags to group these assets and the same goes for ETLs. The list can be filtered and includes shared assets by default but regardless it quickly becomes a mess that only encourages redundant asset creation due to low…
-
In Domo Workbench select column, * not returning data
I'm new to Domo and Oracle SQL having worked in MS SQL Server until recently. In Domo workbench 'Select column, T.* from Table T' doesn't return any rows of data in the preview window. 'But Select column from Table T' and 'Select T.* from Table T' both do return rows for the preview. I thought my issue might be PL/SQL…
-
SQL Dataflow - Left join reduces number of rows in left table
Hi, I am trying to join 2 tables based on a "date - product" —> columns "year_month_key" and "year_month_lagged_key" My 2 tables are the following transform_data_lagged transform_data_current when i join both table via the following left join statement, i loose the first rows in the left table, "transform_data_lagged",…
-
Best Practice - Columns to Rows?
Community, My data is formatted per my example screenshot below. Is it best to pivot each location into rows, or is there a benefit to maintaining columns for rows? I will need a total number, and then drill down numbers (I.e. Total Net Profit = 30 > Drill Down > Omaha Net Profit = 10)
-
beast mode
hi everyone, Im in need of your assistance on completing a beast mode please. I have data that lists out site names on each row with service types but I only want sites that list the 3 types - EPIK or Greenix and Survey and Cutover and Test Like the image below: and this is what I have so far but not giving me the correct…
-
DOMO SQL - Formula
Hi Team, The formula below is validating but it is showing "NaN" for all CPM values. For context estimated_youtube_ad revenue and ad_impressions may include the following values: 0, Null values, integers greater than 0. Any potential way to fix this? Thank you in advance! Select* ,…
-
Export Magic ETL Code for External Version Control
In our current environment, we use an external repository (Bitbucket) to maintain version control and the ability to run detailed diffs before making changes to our SQL dataflows. It would be helpful to have a way to export either the code or some representation of the current state of a Magic ETL dataflow to be used in…
-
Group By Issue
I need assistance with identifying transactions that net out to zero in a large dataset. Each row in the dataset represents a unique transaction with its own ID. Objective: Identify pairs of transactions that meet the following conditions: Same client ID Same transaction type ID Same transaction date The sum of their…
-
Creating a Beastmode/ Dimension
I have a set of data that includes columns like " Facebook Engagements, Facebook Impressions, Twitter Engagements, Twitter Post Impressions, TikTok engagements, TikTok Impressions" how do I create a dimension or beastmode where you will be able to filter by platform, e.g.: Facebook, Twitter, TikTok.
-
Is there a way to aggregate the output using a FIXED function used in a CASE statement?
I'm a MajorDomo in a large retailer, and I'm working to rewrite our TY/LY Beast Modes to calculate off of a "Week Number in Epoch" column, rather than comparing against Fiscal Week as we move into a Week 53. I previously asked about writing a temp table in a Beast Mode (https://dojo.domo.com/main/discussion/comment/59532),…
-
Add ORDER BY to GROUP_CONCAT Function in Magic ETL v2
Currently, Magic ETL v2 supports the aggregate function GROUP_CONCAT in its Group By tile. While clauses inside the GROUP_CONCAT function like DISTINCT and SEPARATOR are available, the ORDER BY clause doesn't work and causes the tile to fail. SQL DataFlows currently support using the GROUP_CONCAT function with the ORDER BY…
-
Get MySQL on latest version
Currently, Domo's MySQL is on version 5.6, which is approaching end of support. There are also a number of commonly used features that people want to use in MySQL, but can't because it is still on version 5.6. Please upgrade to the latest version.
-
Beast Mode for "Views in the first 30 days"
Hi there, My team wants to create a beast mode that looks at total views in the first 30 days per asset since the video was published. For example, a video was published on March 1, 2024 and we want to know how many total views did it generate through March 30, 2024. We created a couple of sample beast modes that are…
-
Pivot/Group By - Make Multiple rows into one row and add columns
Hi Community - thanks as always for the consistent great help. I'm attempting to Group/Pivot/Join/Append (not sure exactly what the methodology will be) multiple rows of Patients into single rows with additional columns. Right now, each patient event is on it's own row. So, a patient seen on Monday and then also on Tuesday…
-
SQL To Magic ETL Issue
Domo's use of mySQL as an ETL is frustrating slow. I have a solution that runs in seconds when I duplicate it in MS SQL, but when applied in Domo it takes forever. I need to figure out how to do the following with tiles in Magic ETL. I have created an example using store locations as anonymized data. Assume I have a table…
-
GROUP_CONCAT function in Dataset View vs MySQL Dataflow
I have created a MySQL Dataflow where I was able to successfully use the GROUP_CONCAT function. It is preferred that I use a Dataset View in this circumstance since the underlying data is coming from AppDB and therefore updates every 15 minutes if there is new data. I have copied and pasted the exact query that is…
-
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…