-
Transforming Support Staff Name Column
Hello, ETL question. For context, I am creating a dashboard for self-service reports on customer satisfaction surveys. Typically I would create a drop down filter card to 'Filter by: Owner', but in this case the owner column contains multiple names: IE: Owner Joe Smith, Bob Smith Cate Smith Cindy Smith, Sam Smith Does…
-
datediff at row levels
Hello, I'm doing analysis of our repetitive customers. I want to create a calculation that will show the days difference between order 1 and order 2 based on the company. Each order has its own row. I created rank column, that assigns 1 to first order, 2 to the second etc… How can I create a calculation that will subtract…
-
Joining 3 different datasets to match zip codes
Hi, I have 3 datasets each with a column containing zip codes. I want to use ETL to create a new dataset with 3 columns, all containing the zip codes from each input lined up. Input: Dataset 1 Dataset 2 Dataset 3 1 2 1 2 3 3 3 5 4 4 6 5 Output: D1 D2 D3 1 1 2 2 3 3 3 4 4 5 5 6 Thank you
-
failed to convert value from string to boolean
Can someone suggest why ETL Formul doesn't working ?? I try to get dynamic current quarter data filter from dataset
-
How do I create a calculated column based off information from another dataset?
I have two datasets: 1- contains the rep name and the number of sales they closed for each date. 2 - contains the rep name and the number of calls they made for each date. In a report, I'm summing up the total number of calls made each week by all reps and dividing it by the number of setters for that week. A rep is…
-
Distinct List_Agg
Hi Folks, In Magic ETL, I'm grouping by a key and defining a new column using the formula: List_Agg(customer_name, ' / ') Is there a method to generate a distinct list of customer names by key? I don't want the repeat customer names in this value. All support appreciated Kind regards,
-
Please help me write this filter formula:
How can I write a filter formula where I want to exclude everything that says 'INTERCOMPANY' from 'CUSTOMER TYPE' Column except this one scenario where we keep 'INTERCOMPANY' where there shows 'X' in 'COMPANY' Column. Similarly, how to write a filter formula where I can exclude everything that says 'ENDUSER' from 'CUSTOMER…
-
Time to Number value
I have tried to find a few answers on this but nothing has come up - is there a way to take a time column (00:02:01) and SUM ALL the values for the total Hours? I have tried SUM(HOUR('TALK TIME')) but that is not giving me the correct summarized number of hours. I also tried to do in the ETL but the column keeps coming up…
-
Partition BY, filling missing values
Hello, I have sales data and want to do some calculations on reference quote numbers assigned to those sales. Sales data have unique 'docnum' and there might be 5-15 rows assigned to the same 'docnum', depending on how much equipment is being ordered. If there is a quote behind an order, then 'Quoteref' will have the…
-
Condition Statement needed for ETL magic
Case or if Then fomula is needed Example Column 1 have list of Contact ID's Colum 2-First/Last Name Column 3- have a list of Opportunity ID's I need help getting a Yes or NO statement based on Contact ID whose attached to the Opp ID Any help is greatl appreciated
-
Question of how to mark or pass data for certain amount of records
so I have a table that looks similar to the following below. What I want to do is to label each activity in each record for each property. Only 5 different activity types don't change, but the number of records changes, so I cant count rows.
-
Calculate the number of Business Days in the current calendar year
I need help with a formula I am trying to use in my ETL to calculate how many business days there have been YTD. I was using this and it seemed to work, however, it is now returning a result of 1. Can anyone assist? (DATEDIFF(DATE(CURRENT_DATE()),DATE(CONCAT(DATE_FORMAT(CURRENT_DATE(),'%Y-%m-'),'01'))) -…
-
Ignore some filters (not all) in Beast Mode aggregation
Hello, I have been searching around for a similar posting, but have not been able to find one describing the same question/issue. I would like to calculate a SUM of a column while ignoring select filters applied to the dataset. The column needs to be affected by all filters except for a couple columns that contain multiple…
-
Split columns based on number of characters using regex
Hi all, I need to split a column that contains both a client name and number into 2 separate columns. Some client names themselves contain whitespace, so I cannot split on whitespace and am looking at Regex, though it's been a while since I've used it. All the client numbers are 5 digits long, though they are randomly…
-
WriteBack duplicating datasets
When using the the writeback option in Magic ETL Why does it duplicate the dataset . example below we now have 2 outputs identical. since Domo charge by rows this will have a negative impact on the number of rows we consume waste to be specific. why cannot the original datasource be used .
-
Formula in Magic ETL
Hello, As a newbie in Domo BI, I would highly value your assistance in resolving an issue that I have been unable to solve. I want to write a formula in Magic ETL that does the same function as the one in Excel from the picture: Compate D2 and D3 and if they are the same AND M3 is blank, take the value from N2. If not,…
-
Creating a separate column using magic ETL
Hi there - I am trying to part of a value that is in a column and change it to be the full name. I keep getting syntax errors whehn going through ETL formula tiles. My formula is alot larger than this but this is an example - I have tried = instead of Llike, as well as contains, nothing seems to be working (becuase the…
-
Filling Blank rows with previous non-null value from the same column
Raw Data : Product Values A 23 24 25 B 45 56 76 C 34 D 55 Expected Output : Product Values A A 23 A 24 A 25 B B 45 B 56 B 76 C C 34 D D 55 Here i am trying to fill blank rows with the previous non-null value in same column and i want to accomplish above scenario in MagicETL, any help will be highly appreciated. Thanks in…
-
SQL in ETL
Hi All, I'm currently trying to recreate a MySQL flow in Domo to ETL. One of the first things we are doing in a table is taking out extra characters from a column to create a new trade_id Here is how we are doing it. when I go to add the formula tile and copy and paste this syntax, ETL is saying it doesnt recognize the…
-
Filtering Records Based on Aggregate Criteria
I've been trying to do the following but can't figure out the best way to do it. I have a table that looks basically like this: user_id|ticket_id|inquiry_type I want to filter out rows for a specific user_id when ALL of the rows for that user_id have a value of "false_inquiry" in the inquiry_type field. But if the user has…
-
Can you turn a date range into each individual date in the range?
I have a start date and end date with a total sum of hours to work. These are each 10 days with an average of 3.2 hours and 2.8 hours respectivelivy. Is there a way i can display each indiviudual day of the date range with the work per day? Something like this?
-
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…
-
Calculation Error : Domo is having trouble validating this formula
Hello everyone, We have three SQL formulas that have worked for the better part of a year but twice now will unexpectedly develop a calculation error without any more detail. This stop the flow in our ETL structure. We are trying to transform data in a couple of ways. Below are the three forumlas that are failing in the…
-
append rows in magic etl not working
Hello All, I am attempting to append two data sources through magic ETL. They have the same columns and data type. table 1: A,B,C,D,E,F,G Table 2: A,B,C,D,E When i try to append the two tables by including shared columns, the resulting table looks like this Table 3: A,B,C The table includes the combined A,B,C columns from…
-
REGEX Extracting Names
Hello, Regex question here.. I am attempting to extract names from a string similiar to this but haven't had any luck: Submitter: ["Joe Smith ","Jill Smith"] I would like to retain the commas between names so that I can split these values to different columns afterwards, like this: Desired Result: Joe Smith, Jill Smith Any…
-
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
-
How to remove duplicates without affecting the blank cells?
Hii Team, I have a dataset which is a join of a user name and phone number and their transactions ID however I need to remove duplicate transaction ID without removing the blank cells as if the user has no transaction ID against it. That means that user has not conducted any transactions. Eg: Dataset Sr No Name Transaction…
-
Converting Text column with fiscal periods to a date
Hello, I have a relatively simple dataset with one column that lists fiscal periods, another with some product types, and another with sums of those product types. I'd like to use this data to create a bar variance card, but Domo is currently looking at my fiscal periods column like they're 'text' instead of 'dates.' The…