-
how to define full quarters
I'm using this formula to calculate the average quarterly engagements SUM(Total Engagements) / COUNT(DISTINCT QUARTER(Date) ) When a marketing campaign data falls under full and partial quarters, however, this approach generates a skewed number. I need a way to test for and countthe number of full quarters that a campaign…
-
Sending email alerts to specific users based on dataset email field
Hi everyone, I have a dataset which looks like the following: EmailRunning Latest VersionVersionfred@email.comYes2.1bob@email.comYes2.1angela@email.comNo1.0tran@email.comNo1.5 I would like to do the following: If someone is not running the latest version (Running Latest Version = No) then send an email to the person as an…
-
ETL not running
I have an ETL that's been running for over a year. I made some updates to it and now it will not run, despite the preview showing everything marked as green. The error message says "Saved but incomplete. Cannot be run until all actions are configured correctly." Can anyone advise on what might be going on here? I've…
-
How to add gross profit row into profit and loss pivot table
I am using pivot table to create a profit and loss statement. Please see the following picture: I want to add a row which is called Gross profit below TOTAL COST OF SALES. Gross profit is calculated by subtracting TOTAL COST OF SALES from TOTAL TRADING INCOME. Also, I don't want to create a Gross Profit account and join it…
-
Rank Tile Not Grouping, Needs Case Sensitive box
I am using the Rank tile, ordering on a Timestamp and the column that makes up a partition is Customer. For example, I have "CUSTOMER A" and "Customer A." These are in the raw data and represent different clients, but the Rank is treating them as the same. I will probably value map one of variations to be unique, and I'll…
-
Fill empty columns based on values in other columns
I'm trying to figure out a way to do the following in an ETL. Given data that looks like this, I want to propagate the email address from the first row with a specific ID to all other rows that match that ID. I need to do this in order to attribute all interactions of all sorts to a specific user's email address: Input…
-
Label ranked rows based on consecutive condition
Hi Folks I'm looking to label multiple rows but only when they consecutively reach 8 or more Rank criteria. I'm ordering the rank on the date, (magic ETL) and partitioning by rule2_hours. When the Rank reaches 8 or more, I want each row leading up to the highest number to be labeled - see the colour field. All feedback…
-
Dividing Two Grouped Columns
How do I divide two columns that are grouped? The Revenue Column is taking the sum of all revenue for each D9-THC bucket. The same applies to the Quantity Sold Column. However, the Days column takes the distinct count of days where these products were sold. I would like to create a new column "Revenue Per day" that divides…
-
Extracting digits from the String
Hello, I'm trying to create a beast mode that would allow me to extract 5 digits that are always coming AFTER words 'Purchase Order'. My issue with it, there is a variable number of digits before depending on the row of data. As an example string might looks like: ABCDSFF Purchase Order 12345 duewobfd ADS Purchase Order…
-
ETL Blank values help
I have 2 datasets I am appending together and want advice on how to handle null values. For example, one dataset has ID,Name, CustID and one is just ID and CustID. How would I use ID in order to fill in another column with 'Name'. For example: Looks like: ID Name 1 John 1 (Null) Want to look like: ID Name 1 John 1 John
-
Create a Function to extraxt a specific secuency of numbers
Team , Connecting a data from salesforce, the dataset came with this challenge: Do any want have a idea to extract for this the secuency number " >5192438<" <a href="https://eomprd.health.ge.com/eom_prd/secured/ordquery.ctrl?hidGONSearch=true%26hidGONSearchStatus=true%26txtGONSearch=5192438%26hidTxtGonSearch=5192438"…
-
Rank and Window? Lag?
So I have a dataset that captures quarterly expense data. I have been tasked with adding an 'expense ratio change column' Basically, I need to know the % change of expenses changed from quarter to quarter by project I know the math on this ((p2 - p1) / (p1)) but am not sure how to tile it in domo to make sure it is taking…
-
How to mix and match Data
Number City 1000 A 1100 B 1200 C 1300 D E 1400 1500 F 1300 Hi Folks, Need your help pls. 2 Questions - 1) In the table above, you could see that 1300 is mapped to city 'D' but in last row it is blank. How do I map it using beast mode or Magic ETL to fill by Lookup in the same column. Req: Lookup in 'Number' and fill Blanks…
-
WEEKOFYEAR() should be removed from the approved function list or it's behavior should be changed
Having a function to act as a synonym for another function is pretty misleading. Especially when that functionality is different from other implementations of the function. Make it so WEEKOFYEAR() returns the week number for a given date while assuming the week starts on Monday. Otherwise the function should be deprecated.
-
Is there a way to duplicate a value one row down?
I have a column with a numeric value and null value every other cell. I want to replace the null cells with the numeric value that is directly above it. Thanks in advance!
-
Weighted Average Calculation
Hi, I'm working with a dataset and am wanting to make sure I am going to get a weighted average of our mortgage rates. We have loans that range from 100k-400k and each loan has an associated mortgage rate. As an example, we have two loans that go into the same security that are 5% and 5.5% Loan A is worth 200k and loan B…
-
Where can I find the Description when you save a dataflow
I would like to create a card showing the changes to dataflows and the description. I have looked in domo stats and also domo governance and dont see it. I am looking for the comment you add before you save. Not the description of the dataflow that you put along with the title.
-
Combine tables using ETL inner join
Hi everyone, I'm trying to combine two federated tables using the inner join in Magic ETL. Both tables have over 9.5M rows but when I join them, the result is only 87K. I tested the join in Snowflake and it resulted in 9.5M by the logic: SELECT * FROM 'A' INNER JOIN 'B' ON A.X = B.X What could i be doing wrong? *I'll need…
-
Can I revert data that I recently appended to my dataset?
I made a mistake while appending new data into my dataset and I need to revert and delete the last 70,000 rows, can I do that?
-
Append Output dataset help
Hello all, I have an Magic ETL (A) that with a output dataset - say OUTPUT_1. What I am looking for, is when every time I run the ETL, I need the output results to be appended. At the same time it should not create duplicate records. I created a new Magic ETL (B) with this OUTPUT_1 as input dataset and outputted to…
-
Question on Domo SQL syntax
Hello all, I am trying to do Magic Transform using SQL option. I have embedded a SQL query but it is erring as it is not compatible with Domo syntax. Appreciate your help in this regard. DATE_PART('WEEK',CAST(TO_CHAR(TO_DATE(TO_CHAR(PRODUCT_KEY),'YYYYMMDD'),'YYYY-MM-DD') AS DATE)) AS "SaleWeek",…
-
Filling in data in ETL for missing dates
I have a bunch of data which shows cumulative reviews on each product (on indirect ecommerce channels). However, if there were no reviews for a particular product on a specific date, my dataset will not show the cumulative review for that product.... so for example, I may have the following data for Product A - in which…
-
Need help mimicking recursion with partitioned datasets
I need help implementing a particular use case for partitions: importing selected partitions from a dataset, modifying the records, then outputting the data back into it's original partition (or a new partition) in the original dataset. There are two uses for this I can think of off the bat: A dataset can have a maximum of…
-
Aggregate and ETL question
Hello all, I have the below workflow in my Magic ETL. Just posting a sample version below. My question is - I have a ProductID that I join from both the data sources and then do a group by and output to a file. But I also need to know Total count of Product ID or row count from TEST spreadsheet source. Is there a way I…
-
Datediff: Total Noobie question
Hello all, I am non-technical, non-SQL speaker trying to figure out DATEDIFF. My problem 1. In Domo, I have a date that looks like this:10/17/2019 11:06:45 AM (this is a 'date' field in my table) 2. I'd like to add a calculated field that counts how many day from "today" it is. Can someone send me the Datediff statement I…
-
GA4 (Google Analytics 4) in Big Query - best practices for unnesting in Domo?
Hey everyone - first post here! Curious if anyone is using GA4 data stored in BigQuery inside Domo, and if so, are you unnesting things after connecting to BigQuery, or are you doing that in BigQuery as a pre-process step? Any best practices to share would be very much appreciated!
-
What is the format for the substring function?
I'm looking for a breakdown of the conditions required for the substring functions. Thanks!
-
Store values from daily average
Hi, I need your help, my brain is frozen…;) I am reporting on Support Cases in Salesforce. Every day, I pull a report into Domo showing all cases per Case Record Type that are open that day. E.g. report pulled 6/9/23 says for CRT Support A 24 Open Cases and for CRT Support B 37 report pulled 6/8/2023 says CRT Support A 12…
-
How to count status changes in snapshot data
I have data that I snapshot daily. It has employee name and employee status. Once the status gets updated, it remains that way: ex for one employee: 1/1 Name: active 1/2 Name: active 1/3 Name: active 1/4 Name: Inactive 1/5 Name: Inactive 1/6 Name: Inactive 1/7: Name: Inactive At the card level, I would like to be able to…
-
How to input identifiers using identifiers
I have a data flow that has multiple properties and competitors. I was looking for a way to label all of the records that are in between into one property. In the example sheet that i uploaded, I was wondering if a beast mode formula could be used to rows 3-16 are labeled as the property Brandon crossroads in this case.…