-
REGEX Tutorial to Validate US Phone Numbers
Hi everyone, I wanted to create this discussion post to share a methodology I created using Domo's Regular Expressions in Magic ETL to parse and validate US phone numbers. This validation process follows the basic standards set by the North American Numbering Plan (NANP) and helps identify valid US phone numbers from a…
-
ETL fails due to "This data set does not support data version filtering."
I have my SQL raw dataset set to merge new rows which is working correctly: This SQL dataset is the input for my ETL attempting to process New rows then Upsert in the output dataset: However my ETL fails to run due to the stated error: Am I setting something up wrong? My raw SQL data is not partitioned but it is using the…
-
Transforming an ETL
Hello! I am trying to edit an ETL with three input datasets.. My goal is to get the 'GLAcct' row of data from the "GLAC PARTS (EZ TEST)' input dataset into the GLBD_V2 datset. Currently, the join is taking the 'GLAcct' under the input dataset 'GLBD_v2'. The request I received was to add a column onto one of our cards…
-
Make Dynamic Date/Time Functions Respond to the Company Time Zone
The dynamic date and time functions—such as CURRENT_DATE() and NOW()—are represented in UTC, regardless of the Company Time Zone in your Admin Settings. This creates a problem during the hours of the day when the UTC date and your company time zone's dates are unaligned. For instance, say you have a Beast Mode that uses…
-
Base64 Decoder
Would like to have Domo be able to decode a column with Base64 data to a JSON formatted column/file/dataset.
-
Exporting Dataset as CSV ignores the Round function
Hi, I have a dataset that I need to extract. In the ETL, I round the column to 2 decimal places. When I extract the data and open it in MSExcel, I see the 2 decimal places. However, if I open it in notepad, I see multiple decimal places. How can I avoid this to happen? Any advice would be appreciated. Thank you.
-
Beast Mode Function Parity in Views
There are a whole host of useful string and number based functions that exist in Magic ETL that do not exist in views. For example, I would like to use STR_DIGITS() in a calculated column to remove all letters from a string. This exists in Magic ETL but not in views. This seems like an oversight.
-
Removing Duplicates and making sure only the one with the most recent move in stays
I am working on an ETL where I am transforming some unit rental records. I am trying to only include the most recent rental for the unit not historical data but the input dataset includes historical data. When using the remove duplicates tile I do not have control over which ones are removed or added as they arent…
-
Views keeps converting time to UTC
Hi All, I am having an issue with a dataset. I am trying to extract an hourly occupancy from people's entry and exit dates and time. However, Views keeps converting the times from Eastern to UTC. I have checked, and my company's time zone is already set to Eastern. I'm not sure what to do from here. If I do an offset in a…
-
Logic - link original submit date to all subsequent back orders
Hello. I'm wondering if anyone has ever had a similar scenario/concept and have come up with good ways to achieve the desired outcome. I've attached a screen shot of real data. The goal is to get the submitted date of 7/5/2024 (A6) linked to orders in rows 7 and 8. The story of this data is that order id 16403748 was…
-
Data storage
Hello, I understand any data coming from files and output datasets from DOMO ETLs shall store data in DOMO Cloud. Any of the following shall also store in DOMO Cloud? DataFlows SQL View (DOMO) Adrenaline Transform What if I use federated connection as my connector? or an API to retrieve data from source and leverage any of…
-
How to Aggregate Daily Data into Weekly Totals in Domo?
I'm working with a dataset that contains daily level data, including a date column. I've created a calculated field based on this date column. However, when I try to aggregate the data by 'week' using 'graph by= week', I'm only able to see the values for each individual date rather than aggregated weekly totals. I've tried…
-
Form Builder and Form Viewer Transformation
I am trying to use submissions we get from forms in DOMO via form viewer/builder apps and turn them into usable datasets that we can create cards with. Right now the form submission data puts the label in one column and the value in another and I would like to make it where the data looks like this: Date: | Model: |…
-
Data Import Help
I have web app that will not share data. No APIs, not even export capabilities. I could use some help with ideas of how to make this work. Here is what the website looks like on the website: And Attached is an excel file where I highlighted and copied the data into an excel sheet in three different way (Paste, Paste…
-
Dynamic data comparison
Hi, I want to perform QTD, YTD without using Domo inbuilt features dynamically. Like, I will have a dropdown for Year using dropdown chart using the Year column from dataset. And variable selector for QTD, YTD. User may select any of the variable selector and any one year from the dropdown. Example: Variable Selector : QTD…
-
Break out rows depending on columns having a value
Hi, I have a dataset that looks like this: Name ID1 ID2 ID3 ID4 A 1 2 3 4 B 5 6 7 C 8 9 D 10 I want to break out these Names by the different IDs, like so: Name ID A 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 D 10 How would I approach this in Magic ETL? Thank you.
-
how to utilize multiple date columns that has values for showing the trend
I have dates for each month for a total of twelve months ( 12 columns), each column has a value under it for every chapter. Now i want to utilize the chapter , the twelve columns to showcase a graph that has trend for year to date. How do I achieve this?
-
Transforming an array of custom fields
I have a dataset currently flowing in from MongoDB which is essentially populated based on custom fields that are optionally set within the source. Each element in the array has the name and value of the custom field (ie. customField<0-N>_fieldName and customField<0-N>_fieldValue). The issue is that it's not always the…
-
Workbench Using File Name in Calculated Field Transform
I am connecting to an FTP in DOMO Workbench 5 and wanted to use the Calculated Field transforms. I want to find a way to create a column with the last 8 digits of my file name as I want to include the date. For example, my file is named "File Name 10172023.xlsx" I want this calculated field to create a column showing…
-
Granularity Issues in Domo
Hello, I have a dataset that I'm working with that has my data at a granularity that is quite large. I then want to be able to group that by each individual session that happens on our website and sum the total time spent for that session. I then want to take the average across all sessions. But I want to be able to do…
-
Showing some values based on SUM, other values based on the most recent date available for the week?
I am working with a dataset that pulls in sales and inventory data for items on a daily basis. In data analyzer, I created a table with the data grouped by week. After grouping by week, most of the fields in my card (i.e. Revenue, Units Sold, etc.) are aggregated by SUM because I would like to know the sum of all of the…
-
Lead Function in Rank and Window Returning Self Referencing Value
For some reason I am getting a self referencing value rather than a NULL. In this example, I was expecting the Next Row Column to be NULL for the last row. Instead, it seems to be self referencing the student enrollment # column. The first 5 rows in the example are correct. Is this the default behavior of this…
-
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…
-
Breaking down bundles into individual items
Hello Everyone, I am currently pulling in sales data from shopify. We have a core range of about 20 products and another 20 or so bundles which are made up of those core products. I am hoping for some assistance with how to break down these bundles into its components (core products) as we would love to see how the core…
-
How do I change the field type for a column on an existing table?
My company recently released a new version of our online platform and I am mapping its new database fields to my already existing Domo reporting tables. In the previous system we used a simple integer as the primary key for all of our tables. In the new version we have switched to UUID values for many of our tables.…
-
Trim 2 characters from right side of string using Workbench
I have a dataset that appends every day and I want to remove 2 characters (.0) from the right side using Workbench. I thought this would be simple, but Search & Replace does not allow replacing with null, and replacing with a space or other character is also undesirable. Is there a way to use any of the Workbench…
-
Domo beastmode and pivot question
Hello, I'm trying to automate a file to run calculation in DOMO on a card. In the attached I added a sample data and a pivot table to show what I'm trying to get to. I was able to get the pivot completed in DOMO, but the additional column Collection Total, I cannot figure out how to create a beast mode for this, I'm trying…
-
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"…
-
Table vs SQL Data Flows and which functions does Domo not recognize? (I'm confused)
Hi, My CSM informed us that window functions are enabled. I would like to write the following via ETL, or a My SQL dataflow (either way is fine), but I don't know whether to pick a Table or SQL transform, and either one I pick will not accept the following statement. SELECT *, sum(a) over (partition by b order by a range…
-
changing a name in ETL
I have an employee name in my raw data shown as >>> last_name, First_name middle_initial Doe, John H. or sometimes shown as >>> Doe, John Dorian H. How can I make it so it would appear as >>> John Doe or John Dorian Doe in DOMO using ETL or beastmode? Thanks