-
Date format question
Hello all, I have a snowflake i/p data source. One of a column is a date field and it showing up as floating decimal in Domo. This field has value -3 (even in data source) if it is a null else it is a date.. As this is floating decimal type in domo, under configuration I changed to Date type. When I try to join with…
-
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…
-
How is Timezone inherited from source then converted?
I have a dataset coming in from the Gong connector and some timestamps come in as text fields (scheduled, started). Our Domo timezone is set to Eastern. When I cast data type as timestamp, the times move up 3 hours. Should I assume the Gong source data is Pacific (Gong is in SF) and Domo is inheriting the timezone…
-
I want to create a column that contains the total sum of another column.
Hi everyone, looking to create a column that contains total revenue in order to find individual contributions. I have tried to do this using rank/window function within the magic ETL however all I get is a running total. Any help appreciated :) Thanks!
-
Error Message on Run when Using 'New' Data Selection
Hi all, I have an ETL with a View of a Dataflow as the input - data selection on input set to 'New' (Beta) then output is set to 'Append (Beta'). The input dataset is 250m rows going back 13 months, so I really only need the ETL to run on the latest transactions coming into the input, not on the whole thing. When I hit…
-
Multi-tab input
Hello, I have a input data set that is a multi-tab spreadsheet. I used File upload option to import that to Domo. When I am using this in Magic-ETL, I am unable to specify the tab that I need.. What is the way to specify the exact tab I referring to in that spreadsheet. I need to use different tabs for different joins in…
-
How to remove whitespace after a full name in ETL
I have a name column with a full name: example Bob Dylan and am joining to another dataset by name. I'm having issues and believe its due to extra white space after the last name. I tried using Replace text, regEx, \s, but that just removed all white space creating BobDylan. Any thoughts here?
-
Can I get the MIN date over a partition with a formula tile?
I need to walk a date column partitioned by the value of another column (UID) to find the earliest date for that group. I do not want to collapse the rows. I've tried a number of approaches to this with a SQL perspective, but I'm starting to think Magic ETL wants me to use a Group by. Here are a couple of examples of my…
-
Preview flow "from here" option?
I think I know the answer, but I thought I'd check anyway … Is there any hack to cause a Magic ETL preview to run from a certain point in the flow forward? I'm working on a flow that starts with some heavy sources and groups down before performing some merges and other heavy actions. I would LOVE to preview from a point…
-
ValueError: The truth value ...
Hi Folks, I'm having an issue with a Python tile in Magic ETL , the code works on my machine (pycharm) but throws an error once in Domo (this happens often, what's the difference?) ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.al The goal is to produce a string value…
-
New Dataflow Run Options
Looks like Domo has silently updated trigger options for dataflows (hopefully its not silent and they tell people, I think it just got to my instance about an hour ago so hoping I am just early and more details will come out soon). I love this idea but do feel it is a bit clunky and requires a lot more clicks but I need to…
-
Using a Power BI dataset
Hello, Is there any way to import a dataset created in Power BI (via Power Query) into Domo as a dataset? For example, my organization uses a Data Warehouse (other than domo). The Finance group, which uses Power BI performs transformations on the data from the Data Warehouse. I would like to use the 'processed' datasets in…
-
Changing dataset update method from Replace to Partition doubles data
We've noticed that if we change the update method of an existing dataset to Partition from replace, we end up with two records in the dataset for every new one (and both of them look identical, down to the field we're using for partitioning): it was quite the shock to see a dataset I expected to have 91 million rows…
-
Issue with Sales Data Tracking and Date Transformation
Hello, I'm encountering an issue with tracking sales data in from our CRM. The problem arises from the fact that the table card relies on the 'Date Created' column for capturing engagements. Although this successfully captures most sales data, it fails to include engagements that were created in the previous year but…
-
Formulae function for table instead of line by line
I have two tables. The first table i have a list of telephone numbers. The second table, a list of the first 3 digits of the number. I want to qualify the first with the next. Here is my formulae: case when SUBSTR(Phone,4,3) not in(NXX) then 'no' else 'yes' END so this is fine in that it qualifies line by line. However, I…
-
Why doesn't the Date Settings feature in Magic ETL update my dates?
When I navigate to Settings and change the Timezone to America/Los Angeles, it doesn't change any of the values for my date columns. However, when I navigate to my input dataset in the ETL and change the time zone on an individual date field, it works as expected. Am I doing something wrong?
-
Data Snapshot
Ok so we have a dataset in one of our databases that periodically gets updated. Every so often, we have new data that gets added AND some of the old data gets modified. (Sometimes we have loans that get modified and we need to keep the original 'value') I am trying to create a snapshot of the original data - on a quarterly…
-
Adding a Python Function or Google Translation into the Magic ETL
Hi, I am currently pulling Datas of Twitter's feed of specific words from the twitter connector on DOMO. As most of the tweets are in Japanese, i was wondering if there a function/calculated field i could use to translate those japanese tweets on the Magic ETL. Do guide me on the process if it is possible. Thank you!
-
Establishing a consistent ID using Rank and Window
Hi, With using MagicETL i wanted to understand if it possible to enforce a consistent ID where only net new rows would receive a new ID associated to it Currently i have a dimension table that contains two columns agency_name and advertiser_name where in the cases of agency_name being unknown it is assigned a unique…
-
regexp_like for pulling specific value out of text string
Hello I need some help pulling out T#### and some T####.### from text string. REGEXP_LIKE('text','/T[0-9]+.?[0-9]+/g', 'n') I am trying to pull the T# out of the following text string and place them in a different column. Data Exfiltration via DNS|T1048.003|T1071.004|T1572 I have multiple rows with different T#'s…
-
HELP! Can not get this ETL Formula to Validate
Can not get this ETL Formula to Validate CASE WHEN HdrParentItemCode IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN' ELSE CASE WHEN HdrParentItemCode IN ('ZTOPOUT', 'ZGAS') THEN 'Topout' ELSE CASE WHEN HdrParentItemCode IN ('ZTRIM','ZDROPIN') THEN 'Trim' ELSE CASE WHEN HdrParentItemCode IN…
-
Fill in Blanks based on existing column
Hi All, Financial Analyst trying to tackle ETLs. Vaguely familiar and can work my way through MagicETL but am stumped on this one. Hoping for some help. I have a data set replicated below. I want to fill the the [BLANK] using an ETL so on the data set where the store numbers are the same and the ticketIDs are the same, the…
-
Single item Filter with CONTAINS functionality
Hi all I have a table of 1,000+ people. Each person = one row. One of the columns shows the companies they have worked for in the past (e.g. 'BT, Shell, Facebook'.) You know what's coming…. I would like to be able to give my dashboard a filter that shows each company once and brings back any person/row that CONTAINS that…
-
Cleaning data off an appending dataset
Hi Dojo, We have several datasets that are appending a decent amount of data each day, and we do not have a need to keep data from as far back as our datasets are holding. Does anyone know of a solution that makes connectors work with a "moving window" to only hold relevant data? ETL solutions work well for our datasets…
-
Chose the newest date available
Hi All, I am working to combine two tables. The first step I need to take is to filter the project_number by with the newest report date available. The example below shows two different project numbers The first one has the last report date as Q3 2022 - we need to capture that input The second project_number has q4 of 2022…
-
Flag for duplicate record
I have two datasets, same structure/columns, 1 large dataset with all records and another large dataset with suspected duplicate customers removed. I want to join these together and create a flag for the suspected duplicate records. I tried to add a formula tile to each dataset, Duplicate = Yes ( for dataset filtered dupes…
-
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…