-
MAGIC ETL/DATAFLOWS Q&A from Domopalooza
The product team enjoyed answering questions from the audience during the final session at Domopalooza. Below you will find the answers to all questions related to Magic ETL and Dataflows (including those that weren't mentioned on stage). Q: When are you going to upgrade the MySQL version in MySQL DataFlows? A: We do not…
-
Mass Renaming Columns
Is there any way for me to mass rename columns? Instead of going into the "Select Columns" tile and going down the list 1 by 1 can I copy paste a text file somewhere that will do this? In any other case I would be fine renaming, but right now my dataset has an output of almost 700 columns.
-
Filtering strings that contain letters
I want to filter out strings that contain letters in my ETL. e.g. Serial 112233 445566PN In the ETL Add Formula Column Name: IsInt CASE WHEN serialLIKE ('%[A-Z]%') THEN 0 ELSE 1 END EDIT CASE WHEN SERIAL~* ([a-z]) THEN 0 ELSE 1 END This is working for A-Z and is case insensitive. Next question, is how do I filter out other…
-
ETL Calculation
Please see above scenario. Those are quotes tied to a project. You can see that quote 90795(column D) is a first revision of quote 90439. My goal is to create a calculation that would show binary output as shown in column E. Basically check every 'Project Name' and if there is a revision to a quote (marked in column B),…
-
How come MySQL does not support window function, specifically lag in dataflows?
Hi, I am currently building a dataflow and trying to get the best out of both sql dialect. I use Mysql to order my data (because redshift doesnt order properly) and then use another datflow in the redshift dialect to be able to use window function. What I am curious about is to know why Mysql does not support window…
-
YTD vs Year Prior
Hi guys, Looked in other posts here but couldn't find the exact answer. I'm creating a table card that would show YTD vs Year prior (Bar charts are not an option since comparing many different categories. The best I could find to show Year prior is SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND…
-
Order by in ETL?
Hi, I am working on taking some mySQL dataflows we have stored and turning them into MagicETL dataflows. I am wondering, does anyone know of an 'order by' function in ETL that is applicable? TIA
-
Beast Mode: Nested Case Statement Help
Hey, I'm trying to select week id base on the current day and I wrote below case statement. But I'm getting error in here. Can someone help? (CASE WHEN week_id <= ( CASE WHEN CURRENT_DATE() BETWEEN '2024-02-10' AND '2024-02-16' THEN 1 WHEN CURRENT_DATE() BETWEEN '2024-02-17' AND '2024-02-23' THEN 2 WHEN CURRENT_DATE()…
-
Compare MAX date with YOY: Month-to-Date (MTD) Comparison
Trying to create a sql statement in magic ETL to compare YOY: Month-to-Date (MTD) by MAX date. My current code from is: CASE WHEN (MONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE) = MONTH(CURDATE()) and YEAR(MEMBERSHIP_INSTANCE_PURCHASE_DATE) = YEAR(CURDATE()) and DAYOFMONTH(MEMBERSHIP_INSTANCE_PURCHASE_DATE)…
-
Making the QTY value to multiple rows
Hello, I am trying to take the data below and make the 'ORDQTY' column multiple rows based on Columns C and T and U. How would I do this? The end goal would be for row 2 would have two identical rows since there are a quantity of 2. Row 7 would have 4 identical rows and so-on. I am trying to have 1 unique row based on…
-
Magic ETL Forecasting Tile
Hi, I have a dataset that I have grouped together into monthly data (using the LAST_DAY function in case it matters). Using magic ETL, I then add the forecasting tile to get a forecasted volume. I have actuals upto the end of May 2022, but the forecast model is only giving me forecast data starting in July 2022. Can…
-
How do I use window function date ranges in DOMO ETLs?
The short version, I need the following query to work somewhere in DOMO ETL: AVG(`Score`) OVER (PARTITION BY Company ORDER BY 'Date` RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND INTERVAL '0' DAY FOLLOWING) More detail: I tried using group by but cannot use window functions, formulas but can't use aggregations, and rank…
-
Creating a Cumulative Concat Function on a Text Field
I currently have an ETL looking at distinct Opportunities by their close won day. However, this issue is now looking at Distinct accounts within the same dataset. At first, I thought to use the count distinct function on the account ID in a groupby. However, since multiple opps can be tied to one account and each one can…
-
Conditionally Starting a Dataflow
I have a dataflow that I only want to start when the data in a dataset materially changes. It seems like it runs no matter if the data changes or not. Any advice on this.
-
Text to Date Issue
Hey there! I am trying to alter a text into a date, but I keep getting the "failed to parse" warning. For context, I'm uploading a CSV file. Does anyone know a formula to fix this? I've tried multiple, but haven't had much luck. *I provided a screenshot of how the field looks in the CSV file prior to uploading into Domo.
-
Magic ETL flow failed while executing with "Failed to index Dataset " error
Hi, I am facing below error while executing magic ETL flow in Domo. Failed while executing "Failed to index DataSet '3a922bde-7139-42c1-96c3-88d7516a1fa7' " All tiles were executed successfully, but output dataset is not created. I used "upsert" as update method for creating output Dataset. Could you please suggest to…
-
Text to Floating Decimal Issue
Hey there! So, I am trying to alter a text into a floating decimal but I keep getting the "failed to parse" warning. For context, I'm uploading a CSV file that already has the field I'm needing as a currency. But, when I upload into Domo it turns it into a text. I will be needing to aggregate (sum) this field so I need it…
-
Using a Power BI dataset
Hello, Is there any way to import a dataset created in Power BI (via Power Query) into Domo as a dataset? For example, my organization uses a Data Warehouse (other than domo). The Finance group, which uses Power BI performs transformations on the data from the Data Warehouse. I would like to use the 'processed' datasets in…
-
Best approach via Beast Mode - multiple if/case statements
Hi everyone, I have the below data example: Name Variety Benchmark Value Achieved Value Apple Red Apple Batch 23 3 4 Apple Red Apple Batch 22 N/A 4 Orange Batch 11 v1 3 4 Orange Batch 11 v2 5 3 Pear Batch 20 TVS 2 6 Pear Batch 24 LKT 4 1 Plum Batch 25 MBH N/A 7 Lemon Batch 2 N/A 5 Ideally I would like to use colors on a…
-
Running Total Beast Mode
Hi DOMO Community, I am working on a project where I need to look at running totals. I have the following beast mode function written out and it seems to be working properly so far: Now, I want to try to fill any null cells. My original approach for this was to create a new beast mode called Incurred During Period where I…
-
Where are the Magic ETL Formula Editor / Add Formula functions documented?
I'm looking for actual documentation of the DATE_FORMAT, DATE_TRUNC, and TIMESTAMP_TRUNC functions.
-
ETL error: "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'"
Hi, I have a magic ETL dataflow. It has 2 input sources. There's a formula tile where we calculate a pacing percentage. (`Delivered Impressions` / (`Campaign Flight` - `Days Remaining`)) / (`Total Goal` / `Campaign Flight`) We've been using this dataflow and formula without any issue for months and now we're getting this…
-
Is there a way to use the LIKE operator to make it case insensitive, specifically in the ETL?
I have looked through the Community Forum trying to find a way to make LIKE case insensitive. I came across a way using ILIKE in the formula tile, but after trying it I found that it didnt work in the Formula. I am specifically looking for a way in the Filter tile.
-
Custom Calendar Week On Dataset?
Hey! What if I want to establish a new calendar on a dataset? For instance, the third week of January would be CW1 and each week starts on a Saturday. thanks!
-
Applying Filter Tile to Remove Nulls Not Working
Any ideas as to why this isn't happening?
-
Capturing a calculation and add the result to a new dataset.
Hello everyone, I am seeking to compute the present amount of accounts receivable at the end of each month and integrate it into a dataset. This will enable us to analyze the fluctuations in these figures on a monthly or yearly basis. However, I'm unsure about the process of capturing this output and creating a separate…
-
Multiple case statements
I have 3 columns in a table with with fruit name , benchmark value and achieved benchmark value. Ideally I would like to use colors on a table when (in this example) : Apple is equal or greater than Apple benchmark value then Green, else Red Orange is equal or greater than Orange benchmark value then Green, else Red Pear…
-
Inline Line Editing Structure
Hello, I am trying to use inline editing to make changes to a dataset by labeling a line item 'YES' or 'NO'. The data I receive is replaced each morning (Some line items are updated, some are removed, some stay the same. ) Currently any changes I make today to the data will be erased the next morning. I have tried doing a…
-
Need help creating Formula to select Invoice Number when it Contains an "A"
I need help writing a Formula for an Aggregated Field in a Group by that will select the Latest Invoice that has an "A" in it if it exists. This example only has 1 Credit and Adjustment (C & A Invoice but I could have multiple to I need to take into consideration the Last Invoice Date as well) Below is my attempt but I…
-
ETL output to Google Workspace
I have one department who just need an ETL tool with no dashboards eg. I know I can get files from google Workspace, do the ETL magic to them - but then can the output be back to Google workspace?
-
Prioritize one set of data over a duplicate set of data, when available.
I have similar data coming in from two different sources. I need to make sure that when dataset A is available, it is used, and when A is not available, B is used. I want to end up with one row of data per day per website, but I either end up with only one day's worth of data per website (Hostname in my data), or I end up…