-
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…
-
ETL Fails = "Illegal Number of Inputs"
I have an ETL that is scheduled to update once per day, but every time it runs automatically, I get a failure after about 10 seconds stating: "Failed while executing. Illegal number of inputs. Expected one but got zero." I usually can rerun it around 2-3 times manually and then it works. There are no errors that I can see…
-
Calculating Time difference in Seconds Beast Mode
Hello, Is there any way to do the difference between 2 timestamps in seconds using a lead function in beast mode. For example in below table take the first 2 rows 9:51:47 seconds subtracting with first row that is 9:51:14 seconds and output should be 33 seconds. The column is in String datatype. Can I achieve this using…
-
Move output dataset from one ETL to another
I'm writing a new version of the ETL that generates our Employee Directory. The existing output dataset from the existing ETL feeds dozens of other ETLs, hundreds of cards, and quite a few external systems via either the SFTP connector or directly through API calls. When I've got my new version all set I'm going to have to…
-
View older ETL version without making that version the active version
I would like to be able to view older versions of ETLs without needing to make them active first. The way I envision it you would go to the Versions tab of an existing ETL and see a new menu item when you click on a version's ellipsis: View Version. Choosing this would open up that version of the ETL in read-only mode,…
-
Previous day status from snapshot data
I have snapshot data everyday that has customers and their statuses. I want to create a formula that looks back one day and reports the status (previous day status) How would I do that in Magic ETL?
-
Point ETL Output to Existing Dataset
I want to be able to point the output of an ETL to a pre-existing dataset. If, as part of a fix or as part of development, I need to make alterations to the ETL to get a desired output that I don't want to be the final ETL, it would be great to be able to make the changes and then point the final ETL to the preexisting…
-
Fill in gaps in data
I have rows of data that look like this: Customer: ID: 12 NULL 12 NULL 12 NULL 12 NULL 12 NULL 12 56 How do I fill in the above rows with the customer ID, 56, Using ETL?
-
Have Domo GPT help with ETL errors
I just spent 30 minutes in an ETL trying to figure out what was wrong. I get the basic errors like "Failed to turn a string into a date", but when I open the ETL and see which tile the error occurs in, I have no clue which specific formula or row is causing the issue. Especially because I will open the root datasources and…
-
Undo button in Magic ETL
The addition of the undo button in the Analzyer has been extremely helpful. I would ask that an undo button be added to Magic ETL as well. This would help people who accidentally delete a tile that had a lot of configuration in it. Or if you made a number of changes that don't work and you want to step backwards to get…