-
Formulae function for table instead of line by line
I have two tables. The first table i have a list of telephone numbers. The second table, a list of the first 3 digits of the number. I want to qualify the first with the next. Here is my formulae: case when SUBSTR(Phone,4,3) not in(NXX) then 'no' else 'yes' END so this is fine in that it qualifies line by line. However, I…
-
Why doesn't the Date Settings feature in Magic ETL update my dates?
When I navigate to Settings and change the Timezone to America/Los Angeles, it doesn't change any of the values for my date columns. However, when I navigate to my input dataset in the ETL and change the time zone on an individual date field, it works as expected. Am I doing something wrong?
-
Fill in Blanks based on existing column
Hi All, Financial Analyst trying to tackle ETLs. Vaguely familiar and can work my way through MagicETL but am stumped on this one. Hoping for some help. I have a data set replicated below. I want to fill the the [BLANK] using an ETL so on the data set where the store numbers are the same and the ticketIDs are the same, the…
-
Chose the newest date available
Hi All, I am working to combine two tables. The first step I need to take is to filter the project_number by with the newest report date available. The example below shows two different project numbers The first one has the last report date as Q3 2022 - we need to capture that input The second project_number has q4 of 2022…
-
unpivoting data
I have data this way: Account | Category | Date 12 | Phone | 01/01/22 12 | TV | 01/01/22 13 | Phone | 01/02/22 12 | TV | 01/01/22 I would like to figure out on each date the counts for each subcategory. First i would want the data pivoted this way: Account | Category | Date 12 | Phone, TV | 01/01/22 13 | Phone | 01/02/22…
-
Does anyone know a Regex that only accepts a segment with a number before 'k' and nothing after?
Does anyone know how to create a Regex that only accepts a segment with a number before a 'k' and nothing after? Or it can be a regex that replaces everything that doesn't have a number before 'k' and nothing after with an empty string. I am looking to extract the circ value for our email campaigns but some rows have a…
-
Pass Variables into a MagicETL from Workflows.
With the new workflow features on the horizon, I think it would be immeasurably valuable to pass output variables from workflows into Magic ETLs since workflows can execute ETLs already. For example: An 'audit' workflow is triggered that evaluates a dataset for various criteria. As part of the evaluation it sets output…
-
Word Count
Is there a way to create a field or value that identifies the number of words in a field? Preferably ETL, but Beastmode if necessary.
-
Sum of units sold, rolling 30 days
Looking to replicate the exact data layout above (side by side columns) in a table in Domo. So units sold by SKU by month (got this far) but also a column for rolling L30 Days sum. I created a new column in the dataset for count of days from current for each row DATEDIFF(CURDATE(),(Calendar Date)) but having a challenge…
-
Averaging time/returning only whole numbers of a fraction number
Is there a way in a beastmode to make the following example numbers on the left below remove the numbers after the decimal? 14.1925 — 14 15.999 —- 15 0.98 — 0 I am trying to get the average time of day that donors answer their phone when we call. So here below this donor answered his phone at these 3 times (hh:mm:ss):…
-
Using partitioning in ETL or Workbench
Good afternoon. Typically, to not do full replaces of data pulls into Domo from our databases, we have used recursive dataflows with ETL. I am trying to think of a way of accomplishing this using the partitioning features in ETL and/or workbench to avoid the ETL still having to process the whole set of updated+historical.…
-
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?
-
Average Time between Orders
Stuck doing this in Magic ETL: Trying to calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer. Customers have multiple orders so I can't use Max(date)
-
Converting Dataflows
Having a "Convert" button in dataflows would be great for switching from one dataflow to another. For example, a MySQL dataflow to a Magic ETL.
-
Creating a rolling standard deviation within magic ETL
I'd like to create rolling standard deviation within Magic ETL that uses the previous 90 rows (or previous 90 days) as the window. I was able to do this within MySQL before, however, I'd like to do this within Magic ETL. For example if I have 100 rows of data each row should reference the previous 90 rows as the sample…
-
Transforming Support Staff Name Column
Hello, ETL question. For context, I am creating a dashboard for self-service reports on customer satisfaction surveys. Typically I would create a drop down filter card to 'Filter by: Owner', but in this case the owner column contains multiple names: IE: Owner Joe Smith, Bob Smith Cate Smith Cindy Smith, Sam Smith Does…
-
NEW Domo Data Science Resources Guide (help w/ Jupyter, scripting tiles, data science tiles, AutoML)
Hi all, I'm a data scientist at Domo on the Data Science Professional Services Team. We have a NEW Domo Data Science Resources Guide. The guide includes a list of all the help resources Domo has created related to data science products (Jupyter Workspaces, Magic ETL R/Python scripting tiles, Magic ETL data science tiles, &…
-
Joining 3 different datasets to match zip codes
Hi, I have 3 datasets each with a column containing zip codes. I want to use ETL to create a new dataset with 3 columns, all containing the zip codes from each input lined up. Input: Dataset 1 Dataset 2 Dataset 3 1 2 1 2 3 3 3 5 4 4 6 5 Output: D1 D2 D3 1 1 2 2 3 3 3 4 4 5 5 6 Thank you
-
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…
-
Is it possible to make a HTTP request using the Python Script tile of Magic ETL?
We would like to use data of a dataset in the body of a POST request and we were wondering if the Python Script tile in Magic ETL would work for that. We have analyzed but it does not seem to have libraries for that. We have this solution to export the dataset and make the request in a Python script in a Virtual Machine…
-
How do I create a calculated column based off information from another dataset?
I have two datasets: 1- contains the rep name and the number of sales they closed for each date. 2 - contains the rep name and the number of calls they made for each date. In a report, I'm summing up the total number of calls made each week by all reps and dividing it by the number of setters for that week. A rep is…
-
Dataset Email Connector
I have a dataset that updates via email connecotr. When the data is sent to the connector the dates turn into integers. Is there a way to fix this? Thanks
-
Is it possible to create a table showing appointment availability in Domo?
I have a list of appointments scheduled with their start and end times, I've created a pivot table showing all appointment start times as the row and closers are broken out into columns. Through an ETL, i'm able to count the number of appointments scheduled at that start time but what I'd really like to do is mark off each…
-
Please help me write this filter formula:
How can I write a filter formula where I want to exclude everything that says 'INTERCOMPANY' from 'CUSTOMER TYPE' Column except this one scenario where we keep 'INTERCOMPANY' where there shows 'X' in 'COMPANY' Column. Similarly, how to write a filter formula where I can exclude everything that says 'ENDUSER' from 'CUSTOMER…
-
Add Hints to Deduplicate Tile
When I ingest the customer table from our POS, for example, and compare individual customer records to the last ingestion, I want to keep the older of the two records if selected fields show the records to be duplicates otherwise. The Deduplicate tile is the obvious choice to use, but appears to choose at random which…
-
Showing some values based on SUM, other values based on the most recent date available for the week?
I am working with a dataset that pulls in sales and inventory data for items on a daily basis. In data analyzer, I created a table with the data grouped by week. After grouping by week, most of the fields in my card (i.e. Revenue, Units Sold, etc.) are aggregated by SUM because I would like to know the sum of all of the…
-
Split columns based on number of characters using regex
Hi all, I need to split a column that contains both a client name and number into 2 separate columns. Some client names themselves contain whitespace, so I cannot split on whitespace and am looking at Regex, though it's been a while since I've used it. All the client numbers are 5 digits long, though they are randomly…
-
Use Empty csv in ETL
Greetings! I am sending csv reports to box and using the Advanced box connector to retrieve the files and add them to a magic ETL. The reports from the current and previous years work great. I would like to set up this process to run through 2026 so I am sending empty reports for 2024-2026. They currently populate box with…