-
Comparing Datasets to Determine Variance
Hello everyone, I am working on comparing data from a previous data input, Universal Analytics, to a new one, Google Analytics 4. Basically, there was an update and data is formatted differently. I am attempting to join the two datasets to compare the month of A ugust for variances, but the closest thing to a unique column…
-
Join Condition in ETL
1.For Some scenarios we will join tables based on the between dates condition with out columns mapping Eg select a.*,b.* from Table1 a , Table2 b where Date between date1 and date2 2.For Some scenarios we will join tables without any conditions Eg select a.*,b.* from Table1 a , Table2 b
-
How to exclude/filter out based on 2 dates
Hello Domo! I uploaded a file/table in DOMO, that has a wipe date and a forecast date. What I was hoping to do is, to use this table and join it in my dataset, so that I can use it to filter out everything within the wipe date and forecast date. Is this possible? I am having problems with joining it to my dataset.
-
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…
-
GA4 campaign and session campaign data Joining question
I have a unique question… some context: I have a dataset from the Google Analytics 4 connector that has 5 columns that I am grouping by with one of those group by columns being the 'campaign' dimension. The other dataset in the join has 4 of the same columns but the fifth one is a 'session campaign'. The two datasets are…
-
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
-
Datasets join issue
I'm trying to create a card that can be used to present to budget owners how much they have currently spent for the month compared to the budget they've been given. In order to do so, I have to create a dataflow that involves joining 2 datasets; One is Netsuite, which stores all the journal entry transactions, and the…
-
How to apply a filter across 2 data sources with filter column having diff names in the data sources
I have a column called Frequency (values could be daily, weekly, monthly) in data source 1 and a column called Cadence (values could be daily, weekly, monthly) in data source 2. How can I join these two columns to apply the filter on both data sources?
-
Combining Rows of Data with a common attribute
Hi, I have a dataset which is product data cross multiple years. In some cases the same buyer bought the product in year 2022 and year 2021 and I want to roll this up into 1 line. How do I do this automatically, matching on the word 'Buyer C' (because it's not feasible to manually match them with the size of the data) Any…
-
MySQL Indexing Issues
When setting up Indexes in my SQL dataflow, I notice sometimes that a few columns in the join are not made available for indexing. Why is that? What can I do to fix this? I suspect without the indexing, my dataflow is taking an inordinate amount of time. Also, in the Domo help on optimizing data flows with indexing, the…
-
Datasets join issue
I'm trying to create a card that can be used to present to budget owners how much they have currently spent for the month compared to the budget they've been given. In order to do so, I have to create a dataflow that involves joining 2 datasets; One is Netsuite, which stores all the journal entry transactions, and the…
-
How to join to a table multiple times?
I'm struggling to replicate a simple type of join my company does all the time in SQL Server within a Domo DataSet view. Perhaps this isn't possible to do yet. I'm hoping someone can help out. Attached photo which has most of the complexity removed, but shows what I'm trying to do. We see "Transactions" in one table, and…
-
How to Deal with Multiple Date Fields
I have a perplexing issue I'm trying to solve. I have data coming from several different data sources (all parts of Zendesk), each of which each represent a different kind of user interaction. For example: Email, Chat, Phone Calls These are all related to a master "Tickets" table, so they can be joined based on a Ticket…
-
Zip2Fips - map card issues
When I try using a United States map card or a State map card, I get the result in the screen shot I've attached. In this example, I've narrowed it down to 2 donors from Illinois (shown in the data table below the card). I am using the column "zip2fips.donor_state_tz" column for the "select county fips code" for US state…
-
MagicETL/SQL to join datasets using a comparison between values
I am trying to get a geolocation based on the IPNumber. Dataset a: ipaddress ipnumber (translated from ipaddress) Dataset b: lowIPNumber highIPNumber Country Region City I would like to add the Country, Region, and City values to an output dataset: ipaddress ipnumber Country Region City where a.ipnumber >= b.lowIPNumber…
-
Looking up values from secondary data source but for multiple columns in primary data source
So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL: -I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource"…
-
Checklist to diagnose elongated runtimes from adding one simple join
I have a complex data flow (A) that was taking about 7 minutes to run. I have another simple data flow (B) that is taking 2 minutes to run. Both have outputs that are properly indexed. I created a new data flow that creates an inner join between two of the output datasets from each data flow (A & B). This inner join is on…
-
Combining datasets by looking for strings in one column
I have a couple datasets... 1) Product IDs with information 2) Google Analytics data with URLs that contain the product IDs on product pages I'd like to join the first dataset to the GA data to include product information that aligns with the product page. Is there a way to join these datasets in which I have the second…
-
Does Domo have the equivalent of a SQL Server "View"?
Maybe I've spent too many years deep in SQL Server. SQL Server has the concept of a "View", which is really just a query, usually containing a join. Instead of having to write a query to join tables, I can just query the view without needing to know the underlying relationships. I don't see any equivalent in Domo that…
-
Help on recursive dataflow
I have a recursive dataflow that keeps getting hung up from an issue on the join between the recursiveprod and the main data that is being groupby. The error it is showing is DUPLICATE COLUMN NAMES. So I go and change the column name and i am able to get it to run. it then takes that new unique column name and adds back in…
-
Beast mode - Join data from another line
Hi Domo community, I would like to achieve a join from a single dataset. Here is the column I have : ID, Parent_ID, Parents_status, Comment 11 21 2 12 21 3 21 Done 1 I would like to do some beast mode to join data between lines and create two type of data: Extract the parents status to apply it to the ID in the column…
-
Long Execution
Hi people! I'm here again! This time, i have a e long execution problem! I have a table with raw data with 46 million lines, approximately. Here is my first question: When a create a dataflow that process that 46 million rows and generates a output dataset, that dataset is a physical table or anytime i use that output…
-
DataSet Views - Outer Join
Hi all, Relatively new to Domo and really enjoying the experience so far :) I've been taking full advantage of DataSet Views and love how easy this facility is to use. One suggestion though. The online information states that "we support Left, Right, and Inner Joins". Why not outer joins though so that the option to retain…
-
Dataflow creation best practice and recommendation
A lot of the dataflow creation I do is taking 2 datasets that differ slightly and don't have an easy 1:1 comparison that makes joining the data very easy. usually I am using 1 report for almost the entire visualization, and puling a column or two form the 2nd report. Since the reports aren't in an identical format, and…
-
Join Function in ETL
Hi, I am trying to join a table on my DataSet, using the left join function. My original DataSet contains only 118k rows, and the other table contains 1405 rows. I thought that the output DataSet will stay 118k rows, and just have addition columns on it. However, the output DataSet is 4.6M which is way more then I…
-
Are "Joins" case sensitive
As we all know, Salesforce record IDs are case-sensitive. Has anyone done a join on Record ID? Are joins case sensitive?
-
Magic ETL join operators
Is there going to be a version of Magic ETL that allows for other operators in a join besides "="? I have a situation where I need to use greater than or equal to and I have to use RedShift for this when I could use Magic ETL is other operators were available.
-
Joining or Appending Data Help
I have a dataset (dataset 1) with individual rows as a specific job (with an identifier key) with details including year and country of operations. I have created cards to show the total number of jobs per year. I have another dataset (dataset 2) which shows historical revenue by Year and Country but it is not specific to…
-
Add Formula Rule to Join Tile in ETL
It has been extremely useful in Magic ETL 2.0 to have the Add Formula Rule available in certain tiles such as the Filter tile or Group By tile. I would like to see it be available in the Join tile as well. This would make it operate more like a SQL join. For example, if I need to use a BETWEEN statement for my join key, I…
-
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…