-
unpivoting data
I have data this way: Account | Category | Date 12 | Phone | 01/01/22 12 | TV | 01/01/22 13 | Phone | 01/02/22 12 | TV | 01/01/22 I would like to figure out on each date the counts for each subcategory. First i would want the data pivoted this way: Account | Category | Date 12 | Phone, TV | 01/01/22 13 | Phone | 01/02/22…
-
Convert text to date field using
Hello, I am using Magic ETL and trying to convert a text field to a date field. Earlier on my data flow I had to alter some columns by using the cast function. I was able to continue with the data flow and have the output I want. It was until I was creating a card that I noticed the "Date" is "Text". (I have some formulas…
-
Issue saving a card
Why does this error occur? Normally it isn't that big of a deal, but I just spent the last 10 mins creating color rules and now I am going to lose all of them and have to start all over…
-
Does anyone know a Regex that only accepts a segment with a number before 'k' and nothing after?
Does anyone know how to create a Regex that only accepts a segment with a number before a 'k' and nothing after? Or it can be a regex that replaces everything that doesn't have a number before 'k' and nothing after with an empty string. I am looking to extract the circ value for our email campaigns but some rows have a…
-
Sort Column in ETL?
I am trying to import some old Data and merge it with data from a connector but the data is in different orders. The legacy data went decending 1 2 3 4 but when I merge the data with the connector its adding to the top so I get 5 1 2 3 4 I was just wondering, is it possible to merge the data onto the bottom or sort the…
-
Duplicates after joining datasets
Hello, I have two data sets, that I ma trying to combine, And After I combine the sets, I get dupplicates Any reccomendations?
-
Word Count
Is there a way to create a field or value that identifies the number of words in a field? Preferably ETL, but Beastmode if necessary.
-
Help with the SUM function, I get "An issue has occurred during processing"
I am going to try to explain myself better, what I am trying to do is identify how many times a person appears in a dataset, in my particular case I need to know the number of people who have created more than 100 contents when trying to run this function, this function tries to count the users that appear or have more…
-
Help with the SUM function, I get "An issue has occurred during processing"
An issue has occurred during processing. We are unable to complete the request at this time. when trying to run this function, this function tries to count the users that appear or have more than 100 records in the dataset SUM(CASE WHEN (COUNT(UserId) > 100) THEN 1 ELSE 0 END) I appreciate any help or opinion
-
Thousands Separator in Beast Mode
Hi Everyone, I'd love to add a thousands separator to the formulas below. I searched and tried the solutions out there for hours, but had no luck. I'd appreciate it if you could help me with this. CASE WHEN `Objective` like 'Awareness' AND `Campaign Name`like '%MOTHERS%' AND `Platform` like '%FB%' AND SUM(`impressions`) >…
-
Average Time between Orders
Stuck doing this in Magic ETL: Trying to calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer. Customers have multiple orders so I can't use Max(date)
-
Creating a rolling standard deviation within magic ETL
I'd like to create rolling standard deviation within Magic ETL that uses the previous 90 rows (or previous 90 days) as the window. I was able to do this within MySQL before, however, I'd like to do this within Magic ETL. For example if I have 100 rows of data each row should reference the previous 90 rows as the sample…
-
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…