-
Removing duplicates
Hi, I need help coming up with logic for this problem. I have a dataset that looks like this: ID Source 1 A 1 A 1 B 1 B 2 A 3 B I want to only remove rows from source A IF that same row is also in source B. Otherwise I want to keep rows unique to source A and unique to source B. The output should look like this: ID Source…
-
Dynamic Pivot
I have a table with a combination of fields and a target value. I want to concat all the fields (Retailer, Field, Target, Strategic Priority) and pivot the table so they are all columns with their respective Target Value underneath. I know how to do this with the Pivot option in which I manually create a new column for…
-
Historical Trend
We have sales data that is just a list of sales opportunities like so Opportunity → Create Date, Close Date, Amount We have all opportunities (open and closed) but we only have the current status of the Opportunity. Can i build a historical trend from this simple data set, so I can see what the total opportunity amount was…
-
Multiple Split Columns
Hey, so i need to split numerous times within a column of data. i'm on the split column tile now, but i don't want to enter 100 new columns to create for each split. is there an easier way to split all of the data in the 'tags' column?
-
Comparing one month's EOM data to previous month EOM data in same row
Hi, I need to do a calculation so I can subtract previous months hours (4/31/2023) data from this month's hours (5/31/2023) data. This is how data is organized. This just a small example of how it is. Without changing the structure of dataset how can i get all the 4/31 data on the same row as the 5/31 data? I have been…
-
How do I alter Date (in Text format : 01.09.2023) to Date format?
How do I alter Date (in Text format : 01.09.2023) to Date format? I need it to be in a Date format such as 2023-09-01 instead of 01.09.2023
-
Date format Changes
Hi @GrantSmith , This is the date format I have in the dataset : I wanted to remove the C and have it in text to alter it as Date : Thus I used this formula on the formula tile (RIGHT(Date, 10)) and got it in : However I tried using the Alter tile and Formula tile (STR_TO_DATE(Date, '%Y-%m-%d')) which did not work, giving…
-
Add a condition on line plot to toggle between AVG and SUM conditions
Hi, I have a column in my data 'Volumes' and I am using that as Y-axis of line chart which is currently set to aggregate as SUM(Volumes). I have a problem with it where I want it to perform AVG(Volumes) when one condition is met and perform SUM rest of the times. I am trying to perform this logic using calculated field,…
-
I need to convert a text column to date in ETL
and I am not sure how to do this, I have attached a screenshot of my text date
-
Approach to logically group data via inference of similar text strings
Hey all. I'm not looking for the detailed solution here as I know it will be complex, potentially require purchased add-ons and most definitely will require my dev team. But I'm hoping someone can guide me to general approaches and Domo capabilities that would be leveraged - if even supportable. We've got a large data set…
-
Keeping only the latest date for unique ID
Hi Guys, I have a question related to ETL transformation. I have a dataset including unique ID's and dates assigned to it. In many cases, there is multiple dates associated with the same unique ID. In my case I want to keep only the latest date associated with each ID. For example I have ID 2 with a date 10.18.2023. If my…
-
How do I filter between 2 timeframes in Magic ETL?
Hey Team! I am trying to filter a dataflow to just show the sales that we had from 10/12/2023 - Current. What filter formula can I use to accomplish this?
-
How to add hyperlink in the table
I have the data of different segments and want to represent it in a single dashboard. To make it user-friendly, I want to make an index table stating all the segments and I want that when I click on the name of that segment or the link (which can be created in beast mode) from the table, to navigate to that particular…
-
Calculate Previous Year metrics in DOMO
I have a requirement to calculate previous year's data (in monthly format - Month Over Month). So if user selects Fiscal year 2023, he should be able to get data for 2022. Only catch is that 2023 means Oct-22 to Sep23. So 2022 would mean data comparison between Oct-21 to Sep22. Any help would be greatly appreciated.
-
I have a column of part numbers and I need to exclude any rows where a part number has a character
Example Parts: '0055656','1248000', 'D9989008','0556767P' I need to exclude rows where the third and 4th part in list above exist. First and last characters can be any member of the alphabet. Thanks, Mike
-
I have a file that has Date, Batch_Id and Active users. I need to keep the latest row in the file
I am trying to do this with magic ETL but I am not having much luck.
-
Identifying if a character in a field is a number or text
I have a row that contains model numbers such as: YM103Kl - (model category would be YM and capacity is 103) YAC24LT (YAC and 24) NC33KLT (NC and 33) I'm trying to figure out how to extract the initial letters (2 or 3 depending on the model) to identify the model category and the subsequent numbers (single to triple…
-
extracting letter out of field
I have a row that contains model numbers such as: YM103Kl - (model category would be YM and capacity is 103, and capacity multiplier is K) YAC24MT (YAC and 24) NC33KMT (NC and 33) I'm trying to figure out how to extract the capacity multiplier. @GrantSmith helped me to identify the model category and capacity with the…
-
How to show monthly user engagement scores for 12 months preceding customer churn
I have a data set that has a row for each customer each day with their rolling 30 day average user engagement score. So columns would look something like customer_id, customer_name, engagement_score, date. In a separate table I have customer churn data, with columns along the lines of customer_id, customer_name,…
-
Confusion - LOWER
I have formula logic to try to standardize insurance company names. I use a series of case conditions to try to create a new field with standard naming. But my logic doesn't seem to work the way it would in SQL. My formula contains searches such as… CASE WHEN LOWER(insurance_company_name) LIKE '%aetna%' AND…
-
etl issues
CASE WHEN "SUBSCRIBERS" < 10000 AND "Youtube engagement rate" >= 3 THEN "Micro_HighEngagement" WHEN "SUBSCRIBERS" < 10000 AND "Youtube engagement rate" >= 1 AND "Youtube engagement rate" < 3 THEN "Micro_MediumEngagement" ELSE "Micro_LowEngagement" END it is failing and saying column not found , how to resolve this , please…
-
Fill down values in a column in DOMO ETL
I am trying to fill down values in a dataset that I have via the ETL process and was wondering if there is an option to do so in DOMO.
-
Grab value from one column and add it to another dataset?
This is probably a simple question but it eludes me since I don't use DOMO regularly. I have 3 datasets. A, B, and C Dataset 'A' has all the metrics for a specific ad set ID but no information for the campaign. No campaign ID or campaign name. Dataset 'B' has all the high level details for the ad sets which include the ad…
-
Not in formula filter on ETL
Hey, I am attempting to use the not-in function to filter out reversal charges. I have a reference column and a memo column. The reference column has a transaction ID and I want to filter out the charge and the reversal. I am writing a filter Memo Number not IN(Reference Number) but it only outputs the reversal only. see…
-
Totaling Summary Numbers on Domo Page
Hey team! I'm not sure this is possible, but I wanted to reach out to the community just in case there's a workaround. We have a Domo page that tracks employee data errors when certain fields in our payroll provider are missed during new employee additions and/or position changes. Based on the screenshot, you can see that…
-
Odd issue with ETL and missing data
I have an ETL I'm working in which data from one of the input datasets is not being pulled through when I run the ETL. Oddly however, I can see the data in preview. In particular, the "Test 1" output dataset will show data when I view it in preview (see screenshot below), but the resulting dataset does not contain any…
-
Use of Pivot Table When a Column Contains Many Variables
You may have run accross this when dealing with FedEx or UPS parcel data. An invoice can have 100's of packages, and for each package you can have many individual charges - e.g. charge, discount (negative), fuel, residential, over dimension, etc. There are many of these, and they are adding to them all the time. I want to…
-
How do i insert a line break in a cell in Ag Gid Domo table?
Hello all, I have plugged AG Grid framework with Domo. Below is the AG Grid Table with multiple values separated by ',' inside a cell. But I want a line breaker after each value. I tried to replace ',' by '\n' using Magic ETL but it is not working. Can someone please help me here?
-
How do you build a filter for a date range?
I need to build out a forcasting dashboard for my purchasing team. They need to look at the last 3 months sales for some items and I was able to get a filter to give me that range in my ETL. However, the other metric they use for some items is a term called "forward 3 months". This means that they need the next three…
-
Calculating Distance In Magic ETL
I have around 400 locations with different coordinates. I need to find if the distance between any the locations is above 1 mile. Should I use ETL or another tool?