-
Filter on two or more columns
Do pfilters require special configuring of python or something? I want to embed an anchor reference link, to open a drill through page filtered on two or more columns in the row that was clicked. In the example below, clicking the link on the first row would open a page filtered on store=5, salesperson=Ralph. Store…
-
What is the formula to convert Julian dates (format Cyyddd) to regular date - not datetime
I would like to be able to convert Julian dates with the above format to a regular calendar date. I don't need datetime. I can see a post for Julian yyddd, but not with Century and that only also includes a time stamp.
-
Simple Mathematical Equations in Domo
Hello, I am trying to do simple addition, multiplication, and division equations using static variables or numbers in Domo. What is the best and easiest way to go about doing this? Using variables gives me "An issue has occurred during processing. We are unable to complete the request at this time." Using manually…
-
Check if site exists in another dataset, then remove from list if exists - ETL
Hi, I have a list of sites which have been joined from two datasets in an ETL using a field called 'Database Number'. This is working as expected. I want to then check whether this 'Database Number' exists in dataset 3, if it does, it means that we are working on this deal and therefore don't need this included in the…
-
Bug / Incorrect behavior with Convert_TZ() in MagicETL
TL;DR - The covert_tz() is subtracting an additional 5 hours when trying to convert a date/time value in UTC back to its original ET. I have a record that occurred on July 31, 2023 at 08:00:29pm, when recorded in UTC this is '2023-08-01T00:00:29.000Z'. This record has 'America/New_York' as a value in timezone . I wanted to…
-
How can I remove line breaks from a string column in an ETL?
I have a dataset that includes line breaks in some cells. I was trying to use the "Replace Text" action, but I'm not sure how Domo is coding the breaks. '\n' and '<br>' did not work. In Domo, you can't visually see the line breaks, but as soon as I download the CSV and open in Excel, I can see them (screen shot attached).…
-
Concat summary numbers
Hi, I'm trying to use the CONCAT function to combine two calculations into one summary number. I want to put Sites = Count of Panels, Panels = Sum of Panels I have done the CONCAT function but it is multiplying the numbers together it seems, I must have missed something and wasn't sure what: CONCAT(COUNT(Panels),…
-
Case statement not working
Please help with my ETL formular. I'm not sure why my case only works for… WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner' WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter WHEN AllRecruiter IS NULL THEN 'N/C' Below is the code I'm using to populate the 'RecruiterOutcome' column.…
-
Anyone else experiencing issues with ETLs running?
Started within the past hour and my ETL's are in a constant state of running into they error out. I did open up a support case as well.
-
Google Sheets Writeback
I've created some simple ETLs that are supposed to export and replace a dataset on a monthly basis. They are doing that, but each time one of them runs on schedule or if I manually run an ETL it creates a new one row dataset in Domo. Is there a way to stop the writeback from doing this and just update the Domo dataset?…
-
How to split values from one column to multiple columns?
I am working with retailer sales reports and sometimes they are a bit messy. Right now, one our retailers reports sales in this format like this: Product Units sold SKU 1 45 SKU 2 10 SKU 3 94 SKU 4 48 SKU 5 12 SKU 6 19 SKU 7 36 SKU 8 56 SKU 9 48 Total Online 368 SKU 1 56 SKU 2 13 SKU 3 118 SKU 4 60 SKU 5 15 SKU 6 24 SKU 7…
-
View query of tile
Is there a way that you can view what the query would like after you use a set up a tile function in Magic ETL?
-
Pulling String Before & After Characters
I'm trying to trim email addresses after the @ and before the . So for example if your email is joesmith@domo.com. I just want to return "domo." I'm using the formula tile in Magic ETL.
-
Dataflow failed
Hello all, I get the below error after running my dataflow. As this consists for multiple inputs, I haven't still been able to trace the exact field causing it. But I do have a formula tile with this calculation.. " Failed to convert value '-3--3-01' from type 'String' to type 'Date'. " Some days this data flow runs…
-
Domo class for analysis
I've got a new team member who I intend for her to utilize Domo more for the analysis capabilities. Is there a good online module(s) anyone would recommend for her to take (that is preferablly free)? I'm envisioning her utilizing ETLs, Views, and beastmodes to analyze the data.
-
Creating rolling averages in Magic ETL and de-emphasizing recent dates
At the most recent Domo Community Virtual Meetup, Michael Christensen demonstrated how to build out 90 day rolling averages using MySQL while also de-emphasizing the most recent dates in a multi-line chart. I was inspired the demonstration and wanted to create the same thing, but using Magic ETL. In this video, I walk you…
-
How do I take a portion of a column value and push it to a new column?
I have a column for campaign names that looks roughly like this A1234567| Campaign 1 | Google Ads | …… What I need to be able to do is read/grab all the non-space characters before the pipe and put that value in a new column called Campaign ID. In the case above, the new column for Campaign ID would contain A1234567. If…
-
Standardizing Names
I need a way to apply a list of replacement values against incoming data. This is to standardize naming on insurance companies. If the data field says "BCBS of Tennesee", "BCBS of Tennessee", "BCBS of Tenn", Blue C Blue S", etc…the result needs to be in a new field with the result "Blue Cross Blue Shield". Say I have an…
-
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.
-
Wildcard for numbers not characters in Beast Mode or Magic ETL?
I'm trying to create a new column based on the prefix of another column, but the prefix B### is categorized as type B, and prefixes BC and BW are categorized as another type H. E.g. Col_ori Col_new B001 B BC002 H BW001 H The wildcard % (i.e. using LIKE 'B%') will include all prefixes starting with B. How can I distinguish…
-
Is there a way to map enums to values?
I have a method of mapping numbers to text: -1 → "unknown" 0 → "very low" 1 → "low" 2 → "high" 3 → "very high" I want to be able to create a graph (bar, or line, or etc) using these values, but I want the label for the number to show as the associated text. I tried using beast mode to convert the numbers to text, but then…
-
Domo ETL parallel processing to reduce ETL runtime
Hello, I have a python script which is part of an ETL to transform a combination of 3 input datasets. The python script contains multiple if else statements and for loops which is taking aroung 4 hours to complete the ETL pipeline. I want to know if there is an option to parallelize the python script in Magic ETL to reduce…
-
Data transformation ideas around old/new user data
HI, we're migrating from username sign in, to okta that uses email as user id. we have an existing user info data with user id as username (xfsgsbss) but we would have a new data with user id as email(fdhsvbsjsbsjsh@email.com). I want to be able to join both old and new data and make sure the right attributes goes to them,…
-
Using Magic ETL to extract a substring using Regex and excluding remaining strings
Hello! I know variations of this question have been asked in the forums but I haven't been able to find a solution that works for me. I have a column Project Name that contains Projects out of Financial Force that may contain an At-Risk ticket number, which is formatted as: AR-00000 i.e. literally "AR-" or "AR - " followed…
-
How to use Beast Mode to Filter Out Specific Words or Phrases
Hi, I'd like to create a re-useable calc that will filter out certain words or phrases that are found in a field. example: Closed Description field might have the words or phrases below which I do not want my cards to display those records. SPAM, N/A, Dup, Duplicate Thanks, Lisa
-
Can you convert Magic ETL into a SQL Script?
Can you convert Magic ETL into a SQL Script?
-
Adrenaline Dataflow: Inequality Correlated Subqueries Unsupported
Hello, I'm attempting to perform a correlated subquery using an inequality as shown in the below example. SELECT a."Opportunity ID", a."Owner ID", a."Created Date", a."Close Date", a."Snapshot Date", a."Last Modified Date", a."Next Step", a."Competitor", a."Amount", a."Stage", (SELECT COUNT("Opportunity ID") FROM "pushes"…
-
Saving States
Is there a way to save data and possibly append to it? Let me give an example. When I pull data from a database, I would like to pull data and when I pull data tomorrow I want the original data to still be there. So I want to be able to see user counts by region today, and then in a month I would still like to see what it…
-
Joinin on Email
I have 2 contact databases where I need to join them based on both phone number, name, and email. I am able to currently join on phone but the email part I am having an issue with. When trying to join on email it isn't working. What is the best way to join on email address and if possible where emails closely match?
-
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…