-
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…
-
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: |…
-
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
-
Getting a Sum of Values for X Days When There are Gaps in the Dates
We have our dataset of transactions for a large number of SKUs. Dataset is over 1 Million rows. We want to examine the date of each transaction and go back 30 days to get a 30 day average of sales for each SKU based on the date of that record. Normally I would be thinking Row and Window tile in Magic ETL. But here's the…
-
Workbench 5 Crashes w/ custom plugin
Hello Domo Experts, I am having problems trying to create a custom, transform, plugin for Workbench. Workbench 5.0.7: When I use Visual Studio to create a new plugin, with no changes to the default code, and add it to WB, everything is fine until I try to configure the Transforms option. When I do this WB crashes. When I…
-
Creating a separate column in transforming data using magic ETL
I am using formula function to add a column that gives out values based on certain condition. I am using case condition for validating the conditions, and I want it to be grouped by Req Id field. because req Id field contains multiple req Ids and have duplication. Idea behind grouping by is that if any of that condition is…
-
Convert DATETIME (UTC) to specific timezone
Hello, Domosapiens! I have an interesting quandry. I have a data flow (using MagicETL - should I be using something else?) that I am trying to build and convert a date that my MySQL data connector is bringing over. The datetime column comes from an orders table and is called posted_date and it brings the data over as UTC…
-
Improve Dataflow Efficiency
Hello, Domosapiens, I currently have a data flow that is taking 14 min or greater to run. The design is as such: MySQL connector that first runs once to pull in all historical data (7.8M rows). Ex full historical pull query: select * from table; change dataset settings to merge method by changing the query to select * from…
-
DataFlow Transform (i.e. Action) Types in API JSON data
My question: What factors determine the type of a dataflow transform in the api JSON data (e.g. GenerateTableAction, SQL, SqlAction)? Background: I am using DOMO's command line tool to get JSON structured metadata about a DataFlow. Specifically, I am using the list-dataflow command: Putting in the DataFlow id and a…
-
Regards to transforming data prior to joining with another data set.
Attached are 2 different samples of data. Both are in similar format, and have plenty of same columns. The price list is where the bulk of what I am going to use will come from. It is the full inventory for a site. the total number of units, number of occupied, number of vacant etc... It is rolled up in to a single row for…
-
Is there an alternative of distinct on for redshift?
Hey, I am trying to build a dataflow using redshift but distinct on is not supported. It's a simple query: (SELECT distinct on ("id") var_list.* from "indicator" as ind order by "id", "rank" desc) Any suggestions for workarounds?
-
Do not Generate Output Table in transformation not available
Hello, I'm looking how to not Generate an Output Table in a transformation ? I did not have this option neither in the transformation neither the dataflow. In lot of tutorial, I saw that a pop up appear with : MagicETL, MySQL and Redshift and then in the editor there is a dropdown list with thoses 3 same options. I did not…
-
Pivoting data in data flows (Year over year || week over week)
Hi, I have account information on one of my data sets like this: Registrations: Name Date_of_Registration Daniel 2015-12-01 John 2015-10-22 Peter 2014-12-01 I want to use dataflows in order to pivot this data in two ways: #1 New registrations per year 2014 2015 1 2 #2 New registrations per week Week 2014 2015 10 0 1 12 1 1…