-
Sum Orders, Line Count, Sales by day of the week (Friday, Saturday, and Sunday)
Hello, my dataset has 7 columns: Invoice date, location, orders, ... invoice total (see screenshot); I want to sum orders, line count, and sales by day; meaning January 21 2023 Friday's orders =DC 12 ATL's orders + DC 11 MOB's order = 65+0. Friday's sales = DC 12's Invoiced total + DC 11's invoiced total = $10,932.39 + 0.…
-
Magic ETL can use Federated Dataset
Hi, We have number of cases where we want to use Magic ETL using federated Dataset. Can discuss them? Thank You. Best Regards, Tejal
-
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…
-
String Operation Add Space to Postal Code
Hi All, I cannot seem to find out how to add a space to a Postal Code Column using new Magic ETL. Trying to change a text string eg: S4L1A5 to S4L 1A5. thx!
-
Timestamp Runs Recursive Dataflow Magic 2.0
Hello all! I feel like I have lost it but I am looking for a simple way to timestamp each run in a recursive dataflow. The output dataset is simply appending on top of itself with no exclusions thereby producing a new set of rows based on the latest data available in the source dataset. When I try and timestamp I am…
-
Calculate time between two dates during business hours, excluding holidays & Sat/Sun
Need to calculate the elapsed time/minutes between Start Date and End Date, using business hours of 9:00AM to 5:00PM, excluding Saturday/Sunday & holidays. Anyone able to help? either in Magic ETL or Beast Mode.
-
Reg Ex difficulty
I am attempting to pull out a string using Reg ex. I want to get the card name, so text after the --- card_name: and between measurable_id. I am using .*measurable_id) but it is giving me an error message?
-
Calculated Column if 2 Fields Match
Hi - I am trying to create a calculated field similar to the = or EXACT function in excel - example =IF(TRIM(A27)=TRIM(B27),"Match","No Match") I need to know if 2 columns have fields that match for any value. Specifically, if email addresses are the same in each column. Thanks!
-
Magic ETL - Undo Button
Can we add an Undo Button to Magic ETL. This would be especially helpful when we accidentally delete a tile when clicking to drag it.
-
How to correctly use distinct count and case when in etl (unknown error)
Could someone help and explain what's causing this error? And how to correct it? Thank you
-
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…