-
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…
-
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?
-
Show "Output Rows" by Action in ETL run details
Currently when you view the run details of a MagicETL dataflow, we can see the rows processed, duration, and status of each action in the ETL canvas. It would be very helpful to see an additional column that shows the number of output rows of each step, similar to how we can see the output of each transform in MySQL…
-
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…
-
Ability to remove unnecessary columns directly in the input DataSet before loading all its rows
The functionality I require is already available in the MySQL and Redshift DataFlow… …in this example deselecting "department" and "state" columns. This option would make it possible not to load all columns for all rows in the DataSet. I hope it will be available in the Magic ETL input DataSets as soon as possible. Thank…
-
Find and append a value to a field
I'm trying to align some inconsistencies with our data. What I'd like to do is find any value that does not end with a '/' and append it with that character. Is there a case statement where we can look for a field that does or does not end in a character? I assume if I can do that, I can just append those without with the…
-
Count of Active Employees Month by Month
I have a card to build which should show running graph of how many candidates had an active authorization at a particular office locations during a given month. It should be able to show historical running totals of employees that an office has and are they growing or shrinking. So, I have dataset which has columns like…
-
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…
-
Having Multiple Group By tiles?
I am looking to have two separate groupings in one ETL. Is there a way to do this without having to use two Group By tiles? Also I no longer see the minimum aggregate option for my new column. Do I now have to use the Min() formula?
-
New Dataflow Run Options
Looks like Domo has silently updated trigger options for dataflows (hopefully its not silent and they tell people, I think it just got to my instance about an hour ago so hoping I am just early and more details will come out soon). I love this idea but do feel it is a bit clunky and requires a lot more clicks but I need to…
-
ETL Field Search Capability
It would be helpful in an ETL to be able to search for a field name and have the tiles that directly reference that field name be highlighted. Many times when supporting dataflow development we are looking for a field and we have to open every tile to see where that field is referenced - in join, in formulas, in filters,…
-
Need Rank to update in non-standard (?) way
I've got customer records with customer GUIDs, record dates, and member status. If the data for a specific customer looks like this: Date Status 1/1/2023 Active 2/1/2023 Active 3/1/2023 Inactive 4/1/2023 Inactive 5/1/2023 Active 6/1/2023 Active 7/1/2023 Active I want to get a dense ranking that groups the status together,…