-
Adding an "Activity_Date" to a nightly refresh dataset.
I just set up a connection for a dataset that doesn't have any fixed 'date' column. The only date column it will have will be the batch_last_run date. On a nightly basis this dataset will append and start to build history. How can I add a new date column in a format such as 'Jul 1, 2021', so that the new data and old data…
-
ETL
I have a column with multiple categories values, I want to convert this categories values to column labels. How do I do this. In the screenshot attached below I want to convert values of column '123 1' to column labels.
-
How do i trim out an email address from a field with a string?
How do i trim out an email address from a field with a string? Example of Field: Yes, we accept this current pricing and would like to proceed with this order. Thank you, Bob Dole Lab Research Analyst Poss Lab—Dole University bob.dole@some.edu Thanks
-
Dividing in SQL
I currently have this code: categorizing some leads. Select `Year-Month` ,`Lead Source` ,`Product Line` ,`Product Models` ,`Country` ,`Early Stage No Movement` ,`Stage 0 to Early` ,`Early to Lost` ,`Early to Won` ,`Early to Late` ,`Late to Lost` ,`Late to Won` ,Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total…
-
Pivot Help
Hi, I have a situation where a column my have different rows of the same values, but the values are in different orders and therefore they are different buckets in cards, when they're actually the "same" thing. And example of this is in A1:B3 of my attached image, where both rows under the BH_TAT.Antigens column have the…
-
Regards to transforming data prior to joining with another data set.
Attached are 2 different samples of data. Both are in similar format, and have plenty of same columns. The price list is where the bulk of what I am going to use will come from. It is the full inventory for a site. the total number of units, number of occupied, number of vacant etc... It is rolled up in to a single row for…
-
How can I create a new column with extracted numbers in SQL or ETL
I have a column called 'description' which can contain multiple elements. One of them is 'Order #1234567890' (or other digits). I want to create a new column that just extracts the numbers in SQL or Magic ETL (so that I can do Beast Modes etc. on it afterwards). How can I create this column? Numbers may exist in other…
-
Filter Rows in Magic ETL based on Text
I am using the Filter Rows aspect of Magic ETL and having trouble filtering if the text filed ins not one word. For example if I create a filter that says ColumnA equals TESTWORD - I get rows where the columns had the value TESTWORD. If I create a filter that tries to filter ColumnA equals TEST WORD - (space between test…
-
Tenure calculation using differences between 2 dates (magic etl)
Hi, I am creating a new variable using the add formula icon where I am creating time period bins that reflects the tenure of the consultant in reference to today. This is the formula I created: case when CURRENT_DATE() -`CD (Start)` < 90 then '3 Months' when CURRENT_DATE() -`CD (Start)` >= 90 and CURRENT_DATE() -`CD…
-
Why is the Group By tile in an ETL giving me an error message?
I am trying to collapse my data set into a single row for each date, publication and product and get the error message "Action outputs to an invalid number (0) of actions" My data is structured as per below:- Date Product Publication Channel Revenue 1/1 Display Publication A OMP £1000 Where Date, product, publication and…
-
Magic ETL trigger timing - input datasets of varied size
I think I just discovered that if a magic ETL is configured to run "only when datasets are updated", and has multiple input datasets, it will start running as soon as the smallest/quicket input dataset has been updated. If a larger/slower input dataset finishes updating before the ETL finishes running, the ETL will not run…
-
View whole output of ETL actions
When building an ETL, how can I view the whole output of an action within the ETL? After running the preview I can only view 100 rows. Example: 1. I join two datasets (that are created within the ETL) to one without knowing if the join-columns are unique. I want to view the datasets that go into the Join action and the…
-
Select specific columns for join in Magic ETL
Hi, I am using Magic ETL for the first time and I am using two datasets and a left join to combine the two. I only want to left join one column from the 2nd table, but I cannot find where I can select for that in Magic ETL
-
Option to Remove the 10,000 Duplicate Error for ETL
Because of how fast/easy ETL flows are, I prefer to use them for most flows. My problem is that sometimes I hit the following error, "Error joining data. The left input cannot include over 10,000 duplicates. Please switch your inputs, group your data, or remove duplicates before joining". The Dojo suggests that this error…
-
Extract 1 row / few rows from big dataset (>100M rows)
When I try to extract the _BATCH_LAST_RUN_ from a MySQL dataset with 106M rows, the ETL takes 3h20min. Is there a way to extract only one row from the dataset. For the moment, I have to wait until the full 106M rows dataset is load in both Magic ETL and SQL.
-
Change Column Name in a Dataset
Hi Guys, Is there a way to change the column name in a dataset? I'm using Informatica to load in my datasets into Domo. I know you can create an ETL flow but the dilemma I'm facing is: I have a dataset (Dataset_A1) which had 4 columns, account | record | id | amount_given I created a dataflow to edit the column names &…
-
Recalculate the transformation of the dataflow based on the specific time range selected in the card
Hi everyone! I am trying to create a card to show the number of active customers. Active customers are those whose the sum of revenue and the sum of quantity are more than 0. Practically, it means to exclude those customers whose the sum of revenue and the sum of quantity are less than or equal than zero. The reason behind…
-
Merging new and legacy datasets
Hello, Experts! I am reaching out in hopes that I get some guidance on the best approach for building out an ETL. Our company is undergoing a point of sale transition and things are going well. We currently have legacy data in one dataset and new data flowing into Domo separately. My CEO is wanting to see week by week…
-
Remove text without removing decimals
Using an ETL, is it possible to remove text without removing decimals? For example, I have a dataset that includes values like this: 1.0 8.54578 N/A 7.49687587125516 ..... Using "Text Formatting" to "Only show numbers" results in the following output: 10 854578 749687587125516 How can I extract the numbers from a column…
-
Week over Week Metrics - Multiple Stores to Show
Hello, Experts, I have a use case where I am looking to build a card for week over week showing in table format a list of stores in one column and total sales in another column for the given X # of weeks. Based on my reading, it looks like I will have to build a new transform to be able to accomplish this. Can you confirm…
-
Amazon DynamoDB Data ETL
My team recently began to transition one of our key source data systems to be housed on AWS DynamoDB. Our connection to the legacy system is cumbersome to say the least (e.g. use a custom powershell script, workbench, and csv to feed data to Domo)...So, I was excited to utilize one of Domo's API Connectors to DynamoDB,…
-
Add values from Web Form row as Columns to a Dataset
Hi, I want to use a web form dataset to allow a user to enter values for 4 variables. Basically, a single row with 4 columns for the variable values. I want to then add these values as additional columns to each row in another dataset. What is the easiest way to do this? The use case is, I want to be able to use the…
-
Break Line based on character for use in Mail Merge
Greetings All, I am seeking advice on how to add a break line to a string based on a specific character. I was able to get the table to look semi correct using ETL + Beast Mode. REPLACE(`String`,';','<br>') But I am unable to use my mail merge doc because the data doesn't doesn't export to excel correctly. I am a bit…