-
Create Month End Date column with Year & Month columns?
My dataset has a Year & Month columns, I would like to add a 'Month End Date' Date column into this dataset with magic ETL. But each month has different end date so I'm not sure how to go on about this
-
Any Function to decode UTF-8 characters available in Domo?
Hi, I have a URL field in my dataset where the data in certain rows have UTF-8 characters instead of normal html characters… For example…. https://www.theverge.com/2024/11/26/24306815/google-gemini-spotify-extension-rollout-android is showing up as…
-
Removing Dataset Outputs from Magic ETL
We have been cleaning up our Domo instance removing unnecessary outputs from Magic ETL flows. I am assuming these datasets aren't truly being deleted, just disconnected from their source to not get updated any more. Is there a way to find these "orphaned" outputs so that they can be properly deleted?
-
Converting a duration time text field into a number
I have a field from NetSuite called duration, which is a measure of time in this format 8:30 This stands for 8 hours and 30 minutes of work time. I need to convert it from a text to a number so I can create measure. Thank you! Jayme K.
-
Validating datasets that has different formats
I need to validate 2 finance datasets to ensure the numbers match for all GL accounts. Dataset A: Columns: Account_name, Branch_id, MTD, MTD Budget, MTD Last Year, YTD, YTD Budget, YTD Last Year. This dataset is generated monthly from the ERP system, with pre-calculated values. I need to append it each month after adding…
-
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…
-
Magic ETL: EJ Group
In this quick tutorial, I share how to convert Redshift queries into Magic ETL with a single click. I demonstrate how to seamlessly transition complex SQL code into Domo’s intuitive ETL tiles, with minimal adjustments. This tool has significantly streamlined our workflows, reducing Redshift processes from 89 to just 27—an…
-
Magic ETL: Butler AR
Unlike other tools I've used, Domo makes it easy to combine data from various sources—SQL databases, Excel sheets, APIs—and transform it to deliver relevant insights. This feature has been invaluable for providing the exact answers my team needs to drive company decisions efficiently. Use case
-
Connectors - Enhance Scheduling
Currently, the scheduling capability with respect to Connectors is based solely upon UTC (see first screenshot below). The scheduling capability available with respect to Magic ETL 2.0, however, provides the user flexibility to specify timezone (see second screenshot below). Can the scheduling capability with respect to…
-
MagicETL Transform Settings: Timestamp to Date
I can't seem to find a clear answer as to what is happening here, so I'm turning to the community. I have a dataset with a timestamp field, createdAt The dataset arrives to Domo in UTC, and my company timezone settings are converting it to USA\Eastern Time. This is working as expected. Using this as an input dataset in…
-
Input DataSet - Load New data
I am working on a new dataflow using Magic ETL. I have 11 input datasets. All are Workbench jobs with the Update method set to Append and they each have an assigned Upsert Key. I have configured each of the inputs to load New data. When I save my dataflow 3 of the 11 throw an error. "This data set does not support data…
-
Group two datasets and join them
I have two excel files one includes sales data and line items of products sold in each invoice, the other sheet I have is the freight charges per invoice. I want to be able to sum the freight and sales totals on both sheets and group them by the invoice number on a combined sheet. Is this possible? I sort of started with…
-
Case when and formula with date
I'm writing the following formula and it's not distinguishing between the dates correctly. What am I doing wrong? case when `Parent OR AgencyName` = 'Aspire Home Healthcare' and `Date` >= '10/23/2024' then `Total referrals` = 0 ELSE `Total referrals` end Note, my date shows as the little calendar and 'Total referrals'…
-
Filtering and Adjusting Cross-Month Records in MagicETL
Hey Domo Dojo Community, I'm working with a dataset containing reservation start (RSD) and end (RED) dates in the format of MM/DD/YYYY. I need to identify all records where the reservation spans across two different months (i.e., RSD and RED fall in different months or years). My objective is to filter out these…
-
Transform Your Dataflows: Discover Magic ETL's Latest Upgrades
Are your data pipelines working as efficiently as they could be? With the latest updates in Magic ETL, there’s never been a better time to fine-tune your processes. Join us on November 21st for a special webinar where we’ll showcase new features designed to help you optimize your dataflows, enhance accuracy, and boost…
-
A way to see cards housed in app pages
Hello, I'm putting together an ETL where I can identify if a card is housed in a dashboard page or an app page. The domo stats 'Card Pages' dataset, has been great to see where cards are in dashboards, but I don't see that option for Apps. I've pulled the 'App Studio App Pages' dataset, however, it still isn't providing…
-
Keep data in column as constant regardless of date
I have an ETL that includes a weekly upload of new information (employee name, quota, current sales) that is batched on Mondays. The quota is typically the same from week to week. When I use the information in a dashboard looking at last week, it doesn't recognize the quota since it's from the current week. Is there an ETL…
-
Income Statement: How do I join 'Chart of Account' table & 'GL Records' correctly
I have 2 tables: Chart of Account - Right Table: notice that there are Addon Repairs, Restocking Charges, Contractor Discount in 'Cost of Sales Others' 2. GL Records (for all branches from Feb 2024 - Sep 2024) - Left Table Problem: I've tried both Full Join, Left Join (key: gl_account_id) then make a pivot table (filter a…
-
How can I mark a duplicate row instead of deleting it in magic ETL?
I use domo to handle metadata transformations for music tracks, and for publishing purposes it's important that I don't have any duplicate track titles. It can mess with the royalty payouts. So basically I want to mark a row as a duplicate based on specific values in columns/rows instead of deleting it. Ideally I'd love to…
-
Removing Duplicates and making sure only the one with the most recent move in stays
I am working on an ETL where I am transforming some unit rental records. I am trying to only include the most recent rental for the unit not historical data but the input dataset includes historical data. When using the remove duplicates tile I do not have control over which ones are removed or added as they arent…
-
Disable Outputs During Testing
An essential part of any workflow is seeing the entirety of the output dataset. However, with the consumption model, ETLs with multiple outputs have become more expensive and more inconvenient to test. I think it would be a good idea to be able to right click on a line leading to an output and disable it to prevent that…
-
Unpivot
Say my raw dataset looks like this: And I would like to unpivot any column that has a month name is it, so in this example, it's 'Jan 2024 Total Pts Earned' and 'Jan 2024 Pts Redeemed', and the dataset would look like this (add a date column, total pts earned and total pts redeemed column): How can I do this with Magic…
-
Adding source column in appended datasets using Magic ETL
Hello, I am trying to append a 8 datasets together and add a new column to the resulting output that is the source dataset for that row. That column is obviously not present in the original dataset. I just need to know where the data for that row originated so I can filter if needed. I have tried a few things but cannot…
-
How to compare to the entire dataset, and not just the filtered part of the card?
Hi, so I have a beast mode calculation in one of my datasets that looks like this: CASE WHEN SUM(SUM(1)) OVER (PARTITION BY TRACK: Title,LIBRARY: Name,TRACK: Version) > 1 and TRACK: Version = 'Full Mix' THEN 'YES' WHEN TRACK: Version <> 'Full Mix' THEN null else 'NO' end The purpose is to make sure that there aren't…
-
Save ETL With Error
I want to be able to save my ETLs regardless of what state they are currently in. I am frequently in a situation where I can't resolve an issue in the moment but want to save the ETL so my other changes don't get lost in the meantime.
-
Delimiter issue
Hello Team, I am trying to do a ETL in domo but unable to get desired result please suggest. In my input, i have a few columns, the lowest grain in Employee id, I have a column called Value, In this values are given using delimiter "|". In my below example there are 2 instances on "|" but in my data it can come any no. of…
-
Duplicate column feature name change in Magic ETL
The current duplicate column title is a bit confusing. I thought it would create another similar duplicate column like the existing one. However, it only replaces the values of an existing column. It would be nice if you could also allow someone to create a duplicate column without affecting the existing columns.
-
Beast Modes Affecting Card Performance
Hello, I'm curious if anyone else has noticed degraded performance making and editing cards due to the amount of beast modes on the dataset/card? We are trying to isolate this issue with some of our datasets, and try moving calculated field logic down to the ETL to come through as a column. Has anyone else experienced or…
-
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…