-
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…
-
Reg. Expression to replace a list of names with a single character.
I have a column that is in a string format, and of course contains names. Majority of the rows are an empty string. What I am looking for is to take the rows that do contain a name and replace them with a 1. i have been trying to use replace text tile then trying to use a regular expression in the 2nd step, then replace…
-
Changing a value to an integer in the ETL
I have a column that uses an 'x' as a flag, any row that isn't flagged with an 'x' is left as an empty string. What I need is to replace the 'x' with a '1' to be used in a later count. this should be straightforward but I keep getting my wires crossed with the order of operations.
-
Previous day values - Following Day Values Restart Calculation Each Month
Hey guys, I have a dataset that appends each day to include the daily downloads, and resets the calculation on the 1st of every month. This issue is, it's cumulative totals, so if yesterday we had 40 downloads and the following day we had 15 downloads, the total for the following day would come in as 55 total downloads.…
-
DOMO Regex Extraction ETL
I'm looking to extract a work order number which contains the letter W followed by 15 digits (Ex. W571191476628566). The order number lies within a string containing notes content that has been exported into domo. (Ex. "Customer called in to discuss work order W571191476628566, and asked about order status"). In the domo…
-
calculate difference between two dates
Hi, We have one filed name "Inquiry Date". and we wanted to calculate the difference between the two inquiries. we need output like mentioned below screenshot (Difference between two dates) eg: (01/01/21) - (01/01/21) = 0 (02/01/21) - (01/01/21) = 1 (07/01/21) - (02/01/21) = 5 (15/01/21) - (07/01/21) = 8 Please suggest a…
-
Dataflow creation best practice and recommendation
A lot of the dataflow creation I do is taking 2 datasets that differ slightly and don't have an easy 1:1 comparison that makes joining the data very easy. usually I am using 1 report for almost the entire visualization, and puling a column or two form the 2nd report. Since the reports aren't in an identical format, and…
-
Join Function in ETL
Hi, I am trying to join a table on my DataSet, using the left join function. My original DataSet contains only 118k rows, and the other table contains 1405 rows. I thought that the output DataSet will stay 118k rows, and just have addition columns on it. However, the output DataSet is 4.6M which is way more then I…
-
How to split values from one column in to 2 columns?
I have a column that contains values that are 4 digits. I want to divide them up in to 2 separate columns. The first 2 number are the % of the discount, and the last 2 are the length Ideally it would be the % and contain 30, 40, 35, 40 ,etc... Then Length would have the last for digits like 02, 02, 03, 02, 04, etc.. I am…
-
How to delete portion of text in a column in the ETL?
I have a date column that is in this format: The format of the date is fine, but what I am trying to do is get rid of everything including the 'T' and to the right, so that all that is left would be 2021-06-27. If a solution within the ETL is possible that would be great.
-
Using Select Columns to reorder columns in Magic ETL
In Magic ETL, a Select Columns tile was inserted just prior to the Output Dataset tile in order to reorder columns. The columns in the resulting Output Dataset were not in the order specified in the Select Columns tile. Is there something else that needs to be considered so that the column order is retained in the Output…
-
Data Flow help
Hello, Experts, I am humbly reaching out for some help. I have a vendor sending a snapshot of a database table every day and not sending me diffs. I am trying to determine the diffs. The connector type is SFTP CSV. The file they send daily is overwritten daily. I currently have the data connector appending the data from…
-
Improve Dataflow Efficiency
Hello, Domosapiens, I currently have a data flow that is taking 14 min or greater to run. The design is as such: MySQL connector that first runs once to pull in all historical data (7.8M rows). Ex full historical pull query: select * from table; change dataset settings to merge method by changing the query to select * from…