-
Converting string to date
Hi, I know there's a STR_TO_DATE function but maybe I could use more insight because it's not working. I have a date field that looks like this: 8-1-22 I want to convert this to a date. How can I achieve this with a ETL formula tile? Thank you.
-
Update Domo Magic ETLs via API/DomoCLI
Howdy, experts, Is there a way to programmatically edit a Magic ETL? I am willing and able to use Python SDK or the DomoCli Jar tool. If there is a way, is there any documentation out there?
-
ETL formula vs Beastmode - result discrepancy
Good afternoon, I've come across a headscratcher where a formula created within ETL is giving a different result when that exact same formula is created as a beastmode. In the attached image, the columns "island break" and "test1" are the exact same formula, with the former being created in ETL and the latter as a…
-
New Year Filtering Issues by Week / Month
My date filters are on the struggle bus in the new year y'all. CASE when WEEK(`Week Date`) = Week(CURDATE())-1 then 'show' else 'hide' end = 'show' The filter is simply returning this current week instead of the previous week. I assume this is because we are in Week 1, and the previous Week was 52. What's a better formula…
-
How should can i remove Dot "." from a column?
Hello, I have a column with multiple data and i need to remove the dot from all of it while keeping the letters and numbers. What should i use in ETL? Will Regex works? If yes, what should be the formula Example of column data: 12412.oaf.124R 124.1256612.124 saarw.124.1fs
-
Network Access from MagicETL?
Am I correct in surmising that whatever environment runs Domo's MagicETLs restricts internet connectivity? I've got a few Jupyter workspaces that a colleague has set up to do various things with our data--mostly normal/conventional stuff, but he's also doing a few things that require network access, like sending emails,…
-
How would I pull a list of column names that have no data?
Our Salesforce dev team frequently changes field names and without notifying us, so I'm trying to find an easy way to create a report showing all of the columns that contain no data as this is a sign this field was replaced with a new field or is no longer in use and we need to remove it from our ETLs and cards. Is there a…
-
How do I filter duplicative entries when two date fields are involved? (entry & modified dates)
Hey Domo Smarties! [Annotated Screen Cap Below] What's the best way to filter out duplicate rows according to the latest modified date in an ETL? Desired Result: One unique entry by "start date" and "model" with the latest "ob modified date".
-
MAGIC ETL Formula
Hi Guys, I am working on a workflow, I have two datasets I combined using left join. Now I have a new column "Category". Now those that are null in the category column and have have "AB" text on its Product Group name should be identified as "Above Normal" and those that doesn't have should be "Normal". But when I run it,…
-
Calculate Previous Quarter Start and End Date
Hello! I am looking to calculate the Previous Quarter Start/End Date and the Previous Two Quarter Start/End Date. For example: Input: November 8, 2022 Desired Output: Previous Quarter Start Date - July 1, 2022 Previous Quarter End Date - September 30, 2022 Previous Two Quarter Start Date - April 1, 2022 Previous Two…
-
Format Integer or Text String as Date
Hello, I am using HubSpot Connector to create contact reports in Domo. A custom HubSpot Date property is pulling into Domo in a strange format and I can't seem to get this formatted as a date using Magic ETL or Beast Mode formulas that I have come across in other threads. My first booked Calendly date field should in this…
-
Using RegEx to Extract Date
Hi all! I am looking for advice on extracting a Maturity Date from a column containing a string of text, example: string: 12months,MATURITY DATE:23-Jun-2022,BALANCE;$12345,PRODUCT Return: 23-Jun-2022 I am able to successfully select the date by using: \d{2}-[A-z]{3}-\d{4} But am unable to replace this so that it only…
-
Magic ETL | Remove Duplicates Old Raw
Hi, Assume my data like that I want to become If I use Remove Duplicates in ETL, it only keep the old row. What should I do now ? Many thanks
-
how to the find difference between dates ( in days ) that are in one column?
I am currently trying to figure out the days between each customers transaction. I know you can use the DATEDIFF function but what if my dates are all in one column? Any suggestions?
-
3 month moving with current month for a date column
I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR. I know the current month function to get only the current month moving but having trouble…
-
How can I format the date in a YOY chart
Hi all, Is there a way to format the date in a YOY chart? The tables in domo allow for you to customize the dates (1st picture) but I am working with a YOY chart and it doesn't want to let me do the same thing. (2nd) I am trying to take the year off of description at the bottom of the Y-axis and just have the month display…
-
Removing Duplication and Ordering Objects within a Concatonated Field
Hello, Our company offers several different product lines and I am attempting to count how many times each combination is owned. There is a field that comes through to Domo from a SFDC connector with this information, but there is a ton of duplication within as a customer can own multiple of 1 product. Example: I'm…
-
Holidays between two dates
Dataset A contains date_start and date_end. Dataset B is a calendar with a field called is_company_holiday. I need to figure out a way (in Magic ETL 2.0) to create a value in Dataset A that tells me the number of holidays between date_start and date_end. Anyone got any good suggestions? Thanks to previous threads that have…
-
Existing dataset as output
Hi, I was editing my ETL and removed the output dataset part and had saved by accident. How do I add the output back in as it seems to just recreate a fresh dataset with the same name rather than write to the existing one. Thanks
-
How to create a column which SUMS based on the grouping of another column
Hello, I am trying to write an ETL which sums a value based on the grouping in another column (invoice number) which yields a column with the total quantity for each invoice. i have attached a simple table as an illustration of what i am looking to achieve. I do not want to write a formula where i have to continually…
-
Combine columns except when null (i.e., skip null)
Hi there, Is there a way to combine values in multiple columns except when the value is null? For example: This is a small example, I need to combine 8 different columns with a separator without ending up with: cat, , , , , orange, . I'm essentially looking for a DOMO equivalent of TEXTJOIN() in Excel. The values don't…
-
Equivalent of IFERROR(VLOOKUP,IF(RIGHT(- in Magic ETL
Hi Guys, Hope all is good. I am using Magic ETL and trying to create a flow replicating the current logic of my Excel file. In Excel I have 2 datasets. On the Main sheet a column is categorizing using the formula =IFERROR(VLOOKUP(value,range,exact Match),IF(RIGHT(Category,2)="AB","Above","Below)) Now in Magic ETL data…
-
Is copying action nodes from one dataset to another possible yet?
This idea was mentioned towards the bottom of this thread by @MattGo and then @StevenC mentioned that it was a good idea back in 2017. Would save us a lot of time and add a lot of value.
-
Fixed Functions for Identifying the First and Last Connections for an Entity
Hi all. I built the following beast mode to identify the first and last connection for an entity. It validates and works when I lay out the connection ID and the beast mode in a basic table, but it doesn't really work with any other visuals (I get error messages), and when I tried to use this to build a net new column in…
-
Is there a way to combine/transform a Date&Time column into Domo?
So I've built a data set in another program, Midas, that I created a workbench job for and pulled it onto Domo. It has columns such as "IP Start Date" and "IP Start Time" and similar for discharge times etc. An end user is wanting to see patients broke down by Admit date, military time, and specific length of stay like…
-
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…
-
Trimming and converting a Date/Time Field
My goal is to filter all times between the hours of 9PM-5AM, but the data in my field looks like this: 2021-12-31T21:20:49 How would I trim this field to get the time exclusively and possibly convert it to 12-hour format? Thank you for your help
-
Making a bucket list of defects
I am trying to make a bucket for sentence long defects and put them in a column that is easier to filter by and group the defects into a certain defect code. I was able to get this working on a beastmode perfectly but when I try to add this in my ETL it gives me the code Failed to turn value string into Boolean. Here is…