-
How to show events that happened after a selected one?
Hello, How can I show all events individuals have attended after they attended a specific one selected by the dashboard user? Ex. Out of the 200 who attended Conference A in January '24, 100 people attended Conference B in February, 80 Conference C in March, and 110 Conference D in April. I want to show the B, C, D…
-
Any way to filter a column by data type?
I am working with a data set that has multiple contributors. Unfortunately many of them enter data their own way. Specifically, I am looking at Diabetic A1C data. One person might enter it is as 5.765, another 5.76%, yet another 5.764444E3, and then another would enter something as 93 MG/L. Is there any formula or DOMO…
-
Solution Share: Using Python to Bulk Rename Columns and Convert Timestamps
Hey Domo Fam! Wanted to share a success story using Magic ETL + Python scripting to solve a problem. My first use of a python tile! Challenge I'm building a Company Dimension table using Hubspot company data...all connector reports have technical column names (over 450 columns) and all date fields (50+ columns) are a mix…
-
Multiple results encountered for the same location in Pivot Table indicated by ****
Attached is a sample of my data. I am trying to create a pivot table with the following: Rows: location, week, date, shift Columns: count(unique identifier), count(unique identifier) * shift length In my ETL, I created a formula to handle empty values. It determines whether a shift was 8 hours or 10 hours, based on a…
-
Re-"Start"-ing inactive datasets in bulk from the Datacenter Dataset View
When datasets haven't been actively queried in a while, they get auto-disabled, and need to be manually "re-started". When datasets like that are inputs to dataflows, they disable the associated dataflows. But, from the Datasets view of the Datacenter, we have no way of seeing that a dataset needs to be "restarted". It…
-
Transforming an ETL
Hello! I am trying to edit an ETL with three input datasets.. My goal is to get the 'GLAcct' row of data from the "GLAC PARTS (EZ TEST)' input dataset into the GLBD_V2 datset. Currently, the join is taking the 'GLAcct' under the input dataset 'GLBD_v2'. The request I received was to add a column onto one of our cards…
-
How can I remove these multiples?
Hello, I have a Magic ETL that provides open order data based on an item number. One issue I am facing is if I have two or more types of open orders for an item, then they will cross multiply. For reference, I have 3 open internal requisitions and 3 open sales orders for 1 item. Instead of displaying 6 rows (3 IR and 3…
-
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…
-
error while converting text to integer/float
I have a column that is a text column has values in %, $ and -ve values as well. I tried replacing the % ,',' and '$' to have only numeric values to convert it into a float or integer column. However I am getting the below error. What can I do to convert the numbers into numeric column so that I can apply calculations to…
-
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…