-
Adding Space to Postal Code TEXT String in Magic ETL
Hi, I might be missing something but looking for some help to add a space to a postal code text string using Magic ETL. I am trying to change a postal code from S4L1A5 to S4L 1A5. thx!
-
Adding business days to a date via MagicETL
Hi, I'm currently calculating a deadline using a dynamic formula in MagicETL. It looks like this: CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END ) The calculation works great, but I was wondering what would be the best way to update it to 4 business days, rather…
-
Creating buckets for unique ids to fall into, based on the sum of donations of those unique ids
Capturing donor frequency: We want to count the number of donations a donor has given per procedure. The amount of donations is the bucket I want donors to fall into. So, if we had 100 donors give 4 donations, we want those donors to fall into the bucket '4', and then we would count the unique donor ids. The part I am…
-
Magic ETL Formula Editor Tile - Minor glitch
I am just wondering if anyone else has been experiencing this. I couldn't find any related posts. In the formula editor tile, I will literally click on the column name from the list below. When I am done with the formula and click "validate" I get an error regarding the column I selected and it says "table doesn't exist"…
-
Can you find a Beast mode by it's function?
Hi We have a primary data feed and that datafeed contained a 15 digit ID. We recently updated the datafeed to the updated 18 character Account ID and in Domo we used to perform a String Operation that performs a left trim from 18 to 15 characters prior to using a Join Data tile. We wanted to find all of the Beast Modes…
-
Creating complex ranking using certain values within a dimension
Hello all! I am attempting to create a sankey diagram that shows a customers activities leading up to a certain conversion. Each customer can have multiple conversions so I want to rank each activity leading up to a conversion, and have that rank reset after the conversion happens. Having some trouble theorizing how to do…
-
Table vs SQL Data Flows and which functions does Domo not recognize? (I'm confused)
Hi, My CSM informed us that window functions are enabled. I would like to write the following via ETL, or a My SQL dataflow (either way is fine), but I don't know whether to pick a Table or SQL transform, and either one I pick will not accept the following statement. SELECT *, sum(a) over (partition by b order by a range…
-
Unique ID within parameter
I'm working on an ETL. I want to create a unique ID for each row of data with the caveat that the ID should be the same as the previous value if a value 0 appears in a flagged column. I was thinking of doing this through a window function of row number and then a function tile, but I can't figure out how to achieve this…
-
How to remove a decimal in an area dimension column?
I have a column that showing the dimensions of a unit. 5x5, 5x10, etc.. the column is in a text format and the data is coming in to Domo in a strange way, it looks like: What I want is just the more standard: 5x5, 5x10, 5x15, etc.. In the ETL how could i get rid of the .0?
-
Right string operation to remove the timestamp
I have a long date that I have converted to a text. Now I want to use the string operations tile to cut off the tail so I can have a nice clean date. the way it looks now in a string format is: 2022-02-24T12:08:34 What I want is: 2022-02-24 What I have been trying is: But it leaving the date unchanged and that timestamp at…
-
Rolling 30 Day Formula in magic ETL
Hi, I am trying to write the formula above within magic ETL. Column A consists of static values. Column B starts with 0 as the first value and has the formula shown in the image. The formula resets its count every time it gets to a number greater than or equal to 30. The only way I can find to do this is with a LAG()…
-
Pivot tile returning results slightly less than expected
I have a dataflow with 2 outputs. I branched off some appended data to one dataset, and then I took the same appended data and applied a pivot tile (initially I was joining to achieve the values in columns, but that was coming out way off). The dataset that is taking just the appended data is coming out correct, but the…
-
Does anyone know how to convert Google Analytics "Date Hour and Minute"?
Does anyone have a solution they are using in a Magic ETL to easily convert Google Analytic's Date Hour and Minute output into something resembling an actual date time? ?
-
Creating an "unassigned" row for gantt chart visualization
I'm working on a gantt chart visualization for resource assignments, and trying to add an "unassigned" bar to the chart for any time periods when a resource is not assigned to a project. My dataset is generally structured as resource name, assignment name, start date, end date: I've pulled in the domo dimensions calendar…
-
Framed Sum in Rank & Window Magic ETL not treating zeroes properly
Hi there, I'm trying to produce a rolling 12 month total for each month in a dataset, but I'm finding with some of the partitions that I get a negative exponential when I'm expecting it to return zero (all the months are zero - see raw data screenshot). It doesn't seem to happen with every partition that has zero values,…
-
Adding Budget Rows to Actual
Hello, I have an original cases database in DOMO each row has a case submission and case start, and then I later replicate it all the columns and added the budget we have for the year. I had to unpivot this data in order to create graphs that will put both dates in the same axis. I would like to show the budget as a third…
-
Comma Separated Field to Child Table
I've written a connector to query a remote API, the data has come in correctly but each row can have many labels, because the connector will only create one table at a time and I don't want to query the remote API twice I've created a comma separated field. ID, Name, Labels 1, "Hi there", "hi, welcome, hello" 2, "Over…
-
Count snapshot data (for HR Turnover)
I am trying to find a way to count number of rows per month (spanning over 3 years and counting) to get the total number employees for each month in variable form. The data structure is set in a way that the number of rows should be equal to the number of active employees as of a certain snapshot date. I am not sure if I…
-
Issue with dates in ETL
Hello everyone! So we use filters similar to this in Domo and noticed that for some reason all results are being returned. What is happening below is we apply a filter on the rule to only look at 'Y' results that should be only applications hired this year, but instead we receive everything. We are working through it on…
-
How to add pivoted value to new columns by Magic ETL
Hello, I would like to ask how to add pivot value to new column by Magic ETL. I prepared the table as follows which have value of product for 2021 Apr-Jun and previous year of same month. I want to add rows and columns highlighted in yellow as shown on the right table by Magic ETL. The rows that I want to add is 2021 Apr,…
-
Deleting a batch
I have 2 batches in a dataset that need to be deleted. The batches are easy to identify and the 2 just need to be wiped out entirely. I am wondering if it is possible to just go in a delete a batch, and what would be the best way to do this?
-
changing a name in ETL
I have an employee name in my raw data shown as >>> last_name, First_name middle_initial Doe, John H. or sometimes shown as >>> Doe, John Dorian H. How can I make it so it would appear as >>> John Doe or John Dorian Doe in DOMO using ETL or beastmode? Thanks
-
Formula failing to convert value from a type string to a type date
I think I need some help with the order of operations on this. I have recursive dataflow for Yelp data. I am trying to create a 'metric date' column by 1st, Splitting the ' FILE_NAME' column date example 'security_public_storage_account_time_range_report_11_1_2021_to_11_30_2021.xls Then in the next Tile I am using the Add…
-
Foreign Currency Conversion Help
I have a dataset that has amount coming in as "Euro", which I would like to convert to "USD". Does anyone know how to go about this in magic ETL? Preferable I would like to use my own fx rates which as you know is likely to change per month. Is these case statement or other that I can use to get this done please?
-
Need help getting pointed in the right direction for what might be causing inflating counts
I have a table that has details of inventory at each site that illustrates KPI's like Occupancy, vacancy, days vacant, and occupied units. It is put together using 3 different data sources. Price list - This is a majority of it. it is all the units aggregated row by row with those occupied, vacant, and total unit details.…
-
Trying to figure out how to filter out top 20th percent of data by week in domo graph
I'm trying to eliminate outliers from my dataset (top20% highest score let's say) by week. So I'd need to filter out (by rank) top 20% of scores from each week and then graph it on a line graph. My data is dynamic and it changes every day so I can't rank the entire data set. I need to rank by week. I've seen people talk…
-
The correct regex to remove the first character?
I have a column( that illustrates a dollar amount but is in string format. What I am trying to do is to remove the dollar sign ($) that is the very first character in each row, so i am only left with the numbers. I have tried the left/right substring but each dollar amount is different length so I have ran in to issues.
-
Write a Filter Formula using Magic ETL Filter Tile
Hey guys, so I am trying to utilize the filter formula feature in magic etl, but I can't seem to get it to work. When I run the following formula, no data appears. Using Excel, I performed a concatenate on all 517 websites that I would like to be filtered out from my data in order to build my formula. I've also used AND as…
-
How populate a new column with data based on data in other rows
I have a dataset consisting of Google Analytics data. One of the columns identifies their Userid (for our ecommerce portal) if they authenticated. Another column identifies their GA session ID (during the visit to our site before and after they authenticate). I am interested in capturing all the activity for a user before…
-
Is this doable? Seeing percent of total day sales by hour using Timestamp column.
Hello! I have a problem that needs to be visualized and it was phrased to me like this: I need to see what percentage of sales each hour had for the total day last year then I need that percentage multiplied by the daily goal to get an hourly goal for today. So we have this chart right now: this show us net sales today,…