-
Magic ETL - Ignoring multiple Daily average amounts that display on the same dates
Hi, I have a question concerning how to fix an issue I am having when attempting to sum daily averages. Here is the issue that I have. As you can see in the image below. I have dates, IDs, and a daily average amount, set up in a dataset in my etl. In this sample, there are a total of 2 distinct IDs, the way I would like…
-
ETL - How to spread a Budget over two dates within a variable date range.
I have various start and end dates for campaigns. I know how to calculate the # of days between any get set of dates. But, what I am trying to set up in the etl is this - if I have a budget amount, start date and end date, what is the easiest way for me to have the etl calculate the division of the budget over the given…
-
"Documentation Mode" in Magic ETL/SQL
A toggle next to "Try the new Magic ETL" would read "Documentation Mode" and would: Put an opaque film over the whole Magic ETL map Disallow changes to the tiles Enable the ability to draw and color-code sections of the ETL Be able to notate what each section is doing An additional item in the Save menu would be "Save and…
-
String Operations
Hi all, I have a column that either has 7 or 8 digits in it. I need it to have exactly 10 everytime I am working in magic ETL and am wondering how I can create logic to see if the column has 7 or 8 then add 2 or 3 leading 0s to the column depending on which case is true. Any suggestions?
-
Division By Zero - Be Aware - Failed IFERROR, IFNULL, and GROUPBY
When you're performing calculations over 100s of millions or records sometimes you come across division by zeros in which you're hoping the software you're using would manage accordingly, I ran into a scenario in an ETL scenario where it lead to having more than a 10% revenue shortfall in my numbers. Generally in Domo if…
-
Flexible SUM Aggregates With Duplicate Values in Dataset
Hello everyone. I have a data set that looks like this: This data set shows product purchases at the customer level. Thus, product IDs are duplicated in the data set. Here's the challenge. I want to show the total value of products, counting each product value only once in a SUM. I've added a Product Rank column as a…
-
Dashboard Table using previous day values in calculation
Hi, I need to create a Dashboard that calculates oil usage for Engines. Below is the excel table with calculation I need to recreate in Domo. Basically need to take the tank level and oil added to day tank and engine from day before and subtract today's tank level to get daily usage. Also sometimes there wont always be a…
-
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)…
-
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…