-
Data Structure Issue
Short version: how do I check if a field is a valid date? Long version: Using the NetSuite connector, our data has a field called Period. The connector does not allow me to define the data structure. I tried admin » toolkit » schema management. It doesn't hold. If I force the dataset field to a string, the next run it puts…
-
Break out rows depending on columns having a value
Hi, I have a dataset that looks like this: Name ID1 ID2 ID3 ID4 A 1 2 3 4 B 5 6 7 C 8 9 D 10 I want to break out these Names by the different IDs, like so: Name ID A 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 D 10 How would I approach this in Magic ETL? Thank you.
-
Handling "Replace vs. Append" Logic with Email Connector
Hello everyone, I'm seeking advice on configuring the Domo Email Connector for importing reports in a "month-to-date" format via email attachments. I want to a) retain the reports for previous months, but also b) replace the latest "month-to-date" report. Our goal is to achieve a combination of "Replace" and "Append" logic…
-
Failed to Convert Value from String Type to Boolean
Hi! I'm trying to setup a formula in my ETL where when the Registration Date is greater than 06/22/2021 we get the Order Item including the nulls. Before that date, I need the formula to include all order items but instead of nulls, I need it to say 'No Order Item'. So it would look like this originally. Registration Date…
-
Issue with MagicETl Filter Tile
Hello there, I am trying to filter a dataset in MagicETL using the Filter Tile, and have confirmed that the data I wish to filter by exists in the dataset 'Cadence name' by 'Outbound - Short'. Yet everytime I try this by running a preview, I get the response "No rows matched your criteria".
-
Pivoting Data for Financial Statements
Hey everyone! First post here, I'm looking to understand the best method of pivoting data for my Profit & Loss & Balance Sheet, so that I can build key metrics & ratios. Currently data is presented as attached, and ideally I would like the years as row's. Can anyone provide any advice here, I imagine utilising MagicETL and…
-
CSV separator but not
Ok so I have a string of data that has approximately 60 digits in it. There is no obvious delimiter - we just have to know the spot where to seperate. I am working in ETL and am essentially trying to do the SQL function where I start at a certain digit and end at another. I need to do this 7 times over so just using the…
-
Datediff in Redshift vs Magic ETL
Hi there! Im trying to recreate a calculation from Redshift to Magic ETL. Im running into some trouble with a specific formula. In Redshift: DATEDIFF('week',"start_date__c","end_date__c") In Magic: v1 - DATEDIFF(`end_date__c`,`start_date__c`)/7 In Magic: v2 - WEEKOFYEAR(`end_date__c`) - WEEKOFYEAR(`start_date__c`) Showing…
-
Data Science - Outliers
Hi, New to using the data science tiles in ETL. I have a dataset with sales by category by day. I can run this through the outliers tile for one category and it will correctly mark the outliers. What is the best way to do this per category? Thanks
-
Rolling 12 Month Average in Magic ETL
Hi there! Was hoping someone would have a solution for what I am trying to do. I want a 12 month historical average in line with the current month in my dataset. (Sample datasets will only show 3 month) This is a sample dataset of what I am working with: *"Desired Column" would be the field that holds the historical…
-
Magic ETL Tiles groups for clearer organization and visibility/observability :D :D :D
Although similar to the "Allow users to specify the color of Magic ETL tiles", we sometimes (okay, often) have extremely long and complex Magic ETL's that are difficult to comprehend, especially if you're inheriting them or exploring for replication or use. Being able to "group" your tiles in some fashion would greatly…
-
Tool to convert MySQL data flows to Magic ETL
In order to take full use of the power Magic ETL v2, it would be great if a tool could be developed to take a MySQL data flow and convert it to Magic ETL
-
UTC to PST conversion in ETL
Hi, I am trying to convert the "Date" from UTC to PST in the ETL. Currently, I created "Send Date PST" with the formula below. TIMESTAMP((convert_tz(`SendDate`, 'UTC', 'America/Los_Angeles'))) However, when I try to use it in the card and filter for the last 4 hours, my card is blank: It works when I do the same thing with…
-
Date Beast mode
Hi everyone, I have a pivot table which contains plan data. The key point is we need to allow one month post plan completion to allow for data collection and input. Country Plan Name Start Date End Date Status Delivered data available Austria Plan 1 1st Jan 2024 1st March 2024 COMPLETED YES Malta Plan 2 1st Jan 2024 1st…
-
lookup function in beastmode?
Hi all, I have a table that has these columns: loan number other loan number first principle balance hi type description 2nd mortgage amount is the field we are trying to create The logic is such: when hi type description = '1st mortgage' and other loan number is > 1 then i want to look up the first principle balance of…
-
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…
-
Enable Preview Up to Point in ETL
Most of the time I don't need to preview the entire ETL. It would be nice to be able to drag/select the tiles of the ETL that I would like to preview up until.
-
Formula to get percent complete
Hi. I have data with Plants and Tasks and whether the tasks are complete are not. I need a formula so I can have a percent completed for each plant based on the number of Yes tasks they have compared to total tasks. Then I need to group by plant and bring that percent into a table. Could someone help with this? The…
-
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),…
-
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
-
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…
-
ETL snap to grid
I like to keep very neat or orderly ETLs. Would love the ability to align ETL tiles horizontally or vertically or snap to grid or something. I think that would help keep a clean workstation.
-
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.
-
Magic ETL Checklist Enhancement Idea
Hello, It would be helpful if we had the option to add items to the Magic ETL Checklist. This would allow for each organization to be able to make sure everyone is designing their ETLs per their procedure/guidelines. For example, if we are requiring the designers to 'label' all tiles, then we could add a checklist item;…
-
Pivot Data in ETL
I have dataset i am trying to reformat using a pivot table. I cant use the pivot chart because it only shows measures as values so i am trying to reorganize my data using pivot in etl. My data includes Facility, Date, and Task Completed (yes/no). Beow on left is how my data currently is and on right is how I want to set it…
-
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…