-
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,…
-
How to SUM working days correctly?
Hi everyone, I have a strange problem. Probably more of a SQL question than a Domo question, but here we go. I have a typical date column (yyyy-mm-dd) and a column called "total working days in month" where it returns a aggregate value of the # of working days for each month. Below is an example (just assume every date is…
-
How to append data to the output dataset?
I have a workbench job that will run daily. It will get the data with current date. I then needs to filter this data and create two data sets using the DataFlow ETL tool. How do I append data to the DataFlow ETL output table? DataFlow ETL creates a new table every time it runs. I realize that One way to achieve this is to…
-
Anything like Difference matrix, Difference between two pivots, difference within the value field?
I've searched around but haven't had any luck finding what I need, but I am not sure if I've searched the best way either so please refer me if this is a duplicate. I'll use dummy data to ask my question, but effectively I need the difference between two values across a couple of dimensions. The goal is different from our…
-
Handling multiple aggregation levels from source data
My company uses syndicated POS data from sources like IRI, NPD, and retailer POS. There are many measures that cannot be aggregated from the item level data, so I need to bring in pre-aggregated data in addition to the non-aggregated data. I was planning to have one column that indicated the aggregation level, but when I…