-
I wanted to rename few items in a column based on this campaign, in the ETL
Hi, I wanted to rename App to Web for this campaign, and I tried using a formula to achieve this in the ETL. Here is my formula CASE when campaign = 'CM1' then 'Web' else channel END But this is not giving me the expected output. Could anyone please help me with this? I actually wanted to achieve this in ETL only, not in…
-
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…
-
First Non-Null Value from Column in Formula Tile
Greetings DOMO, Does anyone know how to find the first non-null value of a column in a formula tile. Tried using Coalesce along with a few tricks and couldn't get anything to work. All help would be appreciated. Cheers, Will
-
Creating a Pass/Fail Test in Magic ETL
I am trying to create a pass/fail test in magic ETL. There are four test it needs to go through to pass or fail. Once a row is listed as fail I would want all the rows matching that submission ID to fail as well how would I go about this or what is the correct formula to use? for example It failed the first test but passed…
-
Cant edit the ETL, beside I'm the owner
Im having some issues with my dataflow,i cant edit the ETL beside im the owner, i dont now what i have to do to solve the issue
-
Allow Window Functions in MagicETL Formula Tile
It would be helpful to be able to write MagicETL formulas using window functions, similar to in beast mode. While the Rank & Window tile is often sufficient, there are some use cases where having this feature in the Formula tile would simplify the dataflow steps: Using a sum window function with unbounded rows This does…
-
Success Rate %
To work out the success rate I need to (a) count number of rows where 'Status' column has 'Application Granted' and divide this by (b) count number of rows where there is a value in 'Determination Date' column. A / B = Success rate Any help would be much appreciated, cheers
-
Form Builder and Form Viewer Transformation
I am trying to use submissions we get from forms in DOMO via form viewer/builder apps and turn them into usable datasets that we can create cards with. Right now the form submission data puts the label in one column and the value in another and I would like to make it where the data looks like this: Date: | Model: |…
-
Question about using recursive reports but only reclusive until post month
I am working on setting up some recursive data structures to capture the data I need for this instance it charges in a period. We can call this resident's charges. Charges are accumulated until a post date which is most of the time the 21's day of the month with some exceptions. I have a small web form that lists these…
-
Daily Dataset Update via Python fails on first run each day
I am using the Domo Dimensions Calendar as an input to a Python script that updates an dataset of the next 365 days as columns |2023-01-27 | 2023-01-28 | 2023-01-29 | 2023-01-30 | ........ | 2024-01-27 So, when the script runs tomorrow the first column will be 2023-01-28, and so on. It works, to a point. The first daily…
-
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…
-
How do i fill in the date gaps?
I have a start date and an end date. How do i have those dates in between appear? (ETLs/Codes)
-
Lead Function in Rank and Window Returning Self Referencing Value
For some reason I am getting a self referencing value rather than a NULL. In this example, I was expecting the Next Row Column to be NULL for the last row. Instead, it seems to be self referencing the student enrollment # column. The first 5 rows in the example are correct. Is this the default behavior of this…
-
String to date
Hi, I'm trying to take the 'DUE DATE' column below (covered by the mouse) and turn those numbers into dates. 20123 is supposed to be 02/01/23 The solution I first thought of was to just split the columns but because it is showing up as text, it wont do that. Is there a beast mode to just pull the last two, the middle two…
-
Any Way to Use Regular Expressions to Change Case of Specific Words
I've got a requirement to change the appearance of articles and prepositions in words in a column to be displayed as lowercase after running a Title Case on the rest of the phrase. Right now, I have an extensive list of text replacements in Magic ETL to do this, but I was wondering if there might be a way to do this with a…
-
Correlation analysis in Domo?
Is it possible to conduct a correlation analysis in Domo using ETL (or other means)? If so, how? P.S. my analysis will include 3+ variables
-
Splitting up multiple-value fields into multiple rows
Hello, I have a dataset like this: ID Num 1 10 2 20, 30 3 40, 50, 60 I want to use Magic ETL to break these out so each row has one "Num": ID Num 1 10 2 20 2 30 3 40 3 50 3 60 Thank you
-
Filtering data will completely remove blank values
Hi community, I'm not sure if this is by design or if this is a bug. Filtering data within a field will completely filter out the blank values as well. It looks like Domo won't recognize the Blanks as a value. This is an scenario: FieldA, FieldB CategoryA,12345 CategoryB,58679 ,89658 CategoryD,97865 CategoryY,74935 Filter:…
-
Regex Replace Issue
Hello, I'm trying to do a regex replace to grab a combination of letters and digits but throw out the rows where I don't get anything. Here is my formula: case when REGEXP_LIKE(`Asset name`,'^.*([a-z]{4,5}\d{6}).*$')=1 THEN REGEXP_REPLACE(`Asset name`,'^.*stc=?([a-z]{4,5}\d{6}).*$','$1') else NULL end To explain, I'm…
-
search and replace
Is there an equivalent to search and replace in ETL? i have a data set that I need to change the word 'Not' to something else - the problem is i only want to change that word and only that word. Any suggestions?
-
separating Output datasets from ETL
I think I made a a mistake of having 1 ETL facilitate some simple transformations for several input datasets. I have the ETL triggered to run whenever any of them update... but now it occurred to me that the ETL is going to keep running every time one of them updates - and I'm afraid it won't pull in the latest updates…
-
Adding data that matches a date to a date dimension table.
I have HR data that has Start Date and End Date (or null) for each employee. I am looking to be able to see the number of active employees at any given date. I have added the Domo Date Dimension table to my data set, but I can't figure out how to join so that for any date I can get the count of employees who's start date…
-
Magic ETL: How to REMOVE Rows Meeting a Two-Column Criteria?
As part of my ETL, I'm trying to REMOVE rows that meet the following criteria: column 1 is NOT NULL AND column 2 is NULL So if column 1 has any value in it AND column 2 is empty, then remove those rows from the data set. I was trying to figure out how to create a Filter Formula for this, but it seems to be designed for…
-
Can someone help with a simple join?
This is a super simple problem, not sure what is going on but I am hoping a fresh set of eyes might help. All I am trying to do is take the 'property_address' column from the powerlender table and use that information onto the foreclosure table - foreclosure is otherwise all ready to go. Both tables have a loan_id (one is…
-
Reporting on a dataset e.g. duplicates, null values and data types
I was wondering if someone could help? I have a dataset containing 196 columns, I would like to know which of the columns are entirely null, which contains data, what level of duplication the field has and what type of data is in the field. There is a similar question here, but this is more about removing as opposed to…