-
MySQL Recursion Performance/Speed Issues
Hello! I am looking for suggestions on how to improve the performance of my mysql dataflow. Currently I have a recursive dataflow built with a Google Analytics connector, and I'm using a concatenated key created by joining multiple columns together to delete and upsert new data from the historical dataset. I'm using the…
-
Appending an update set to itself
Hi, I have a MySQL dataflow that is built from API reports from a vendors application. The idea is that we did a one time historical pull of the API as dataset 1. Then, the same API that only pulls 1 day of data and appends to the historical dataset as dataset 2. But, every once and a while it seems as if data is dropping…
-
access max.date from sql table
Hi. I have two input datasets in SQL Magic Transformation. One table called daily the other one estimated . Both tables have the same columns. I need to fetch the data from estimated table but only for the date that is later than max(`Date`) in table dail Data looks like this. Table daily Date Name Revenue 2021-08-01 A 10…
-
How do I used a Create / Use / output table using a SQL transform SQL type / CREATE PROCEDURE?
I'm trying to just work out the basic syntax / method in DOMO SQL transform of: Create Stored Procedure (creates a result table after some manipulation of the data) Execute Stored Procedure (to get result table) Output Result table into DOMO for use in charts etc.. I'm trying to get this to work but I can't get the results…
-
SQL - join where a column is between a number range
Hello, I cannot for the life of me figure out why this "where" clause is giving me troubles, I have used this logic a thousand times with no problems. The problem is coming on my join of c to a. Specifically, it seems to happen with my "where c.result_code is greater or equal to 9.5 and c.result_code is less than or equal…
-
Join based on filter criteria
Table A (Completed Evaluations w/ dates & locations) Table B (Locations Universe, all available locations) Table A has all the same location columns as Table B. Table A also has completed dates and evaluation data, We want to show locations with completed evaluations for a user specified date period (i.e. using a date…
-
Is it possible to use while/do loops in MySQL in Domo Dataflows?
I have data that has varying rows of data for each identifier, from one row to 26. For each identifier, I have four different columns that change with the various rows. For each new identifier, I want to loop through the rows that follow that have the same identifier and build the row out with the unique values in these…
-
Dividing in SQL
I currently have this code: categorizing some leads. Select `Year-Month` ,`Lead Source` ,`Product Line` ,`Product Models` ,`Country` ,`Early Stage No Movement` ,`Stage 0 to Early` ,`Early to Lost` ,`Early to Won` ,`Early to Late` ,`Late to Lost` ,`Late to Won` ,Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total…
-
Is 'Partition by' supported in DOMO SQL?
I tried to create a new table in DOMO SQL using the following syntax in sql : SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`, sum(sum(case when `Transaction Type` = 1 then `Amount`…
-
Is it possible to do CTE hierarchical recursive query in MySQL?
I have a table with thousands of rows in which there is a relationship of child/parent between the rows. For each record I would like to have all the hierarchy related with that record. As an example, I have the following table and for the id=19 I would like to return all its related children, which are [20, 21, 22] I have…
-
MySQL Data Flows clarification: MySQL version & window functions (row_number)
I've neglected MySQL Data Flows because I'd passingly assumed that they were for MySQL-derived DataSets. It seems not, it looks like a MySQL DataFlow is a DataFlow that uses an embedded version of MySQL. Is this correct? I've seen mention of Window Functions in passing, and assumed the MySQL must be 8.0+, as that's when…
-
DOMO SQL MAX(date) not working
hello, hope someone could help on my SQL: I just created a simple SQL under DOMO MySQL to select some fields and the MIN and MAX of a date, like: SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date' FROM`data_1` GROUP BY `id`, `name` but nothing is returned in the 2 date fields, original…
-
How can I create a new column with extracted numbers in SQL or ETL
I have a column called 'description' which can contain multiple elements. One of them is 'Order #1234567890' (or other digits). I want to create a new column that just extracts the numbers in SQL or Magic ETL (so that I can do Beast Modes etc. on it afterwards). How can I create this column? Numbers may exist in other…
-
ETL SQL Statement
Hi, I'm not familiar with SQL and wondering if someone can help with a SQL statement in an ETL that would solve the below(hope it makes sense)... COLUMN1(txt)COLUMN2(date)COLUMN3(date)datapoint1oct 18 2020if 'column1' = 'datapoint1' then 'column2' minus 1 mth from the datafield in Col2' datapoint2oct 18 2020if 'column1' =…
-
Help identifying and isolating a date held within a string of text
Hi all, I'm hoping some creative minds out there can help with a business problem I am having. I am looking to create a Beast Mode field for expiry date, however the source of the expiry date is held within a string of text. Example: "Building agreement in place with John Smith - signed via docusign , EXPIRES 2020-04-28…
-
SQL Syntax tips for a beginner
Hello, I am new to Domo but want to learn how to use Domo with SQL. I am advanced in R and I understand the logical needs. I was hoping someone had some SQL syntax tips or resources for learning the SQL language to implement in Domo. Thanks!
-
Unknown SQL type - 110
I am getting this error on a workbench job, "Unknown SQL type - 110" Does anyone know how to fix it?
-
SQL Queries for SQL Server Datasource
Hi Team, We are planning to migrate from Cognos to Domo. 1. Issue is we have prebuilt Custom SQL queries with Joins/Unions/logics etc,Views which should be as it is used in Domo. I know using Domo workbench we can create a connection to Onpermisses database but will it allow complex SQL queries which will be of 2-3 pages…
-
Mimic VLOOKUP based on Date Ranges
I'm looking for a way to merge two sets of data based on approximate matches. I have two datasets: one with all of the daily sales data for a customer based on their id number: idDateRevenueGross…
-
Multiple SQL Update Statements in Same Transform
I'm currently adding one Update Statement per one Transform process box/dataflow in the Domo MySQL tool. How would I combine them in the same process box/dataflow in Domo? Thank you. /* Update Value1*/ UPDATE `table1` SET `Title` = "My Updated Value1" WHERE `Title` = "My Original Value1" /* Update Value2 */ UPDATE `table1`…
-
SQL Environment Monitoring Dashboard
Made this SQL Monitoring Dashboard with a couple SQL scripts/logging tools. Shows at a quick glance how your SQL environment is doing!
-
Export .sql queries API
Is there a way to export SQL queries from the UI from domo using an API or extract function? (e.g. I want to backup SQL queries for a dataset, to then version them, as they are edited)
-
Replace in SQL
I have a column which is bringing in some field values in between crotchets or square brackets [ ] and also in between inverted commas " " BUT not all values come through like this. I am trying to write a SQL to drop the [ ] and " " in the front and back of the values. im using REPLACE(REPLACE(`COLUMN NAME`, '[', ' '),…
-
sql dataflow turn off input dataset limit
In sql data flow can I turn off the input dataset loaded limit? cuurently it set at 500K. I have big tables where the first 500K rows are all the same date so if I try and do a select distict date, I get a single row in my preview. I have long, advanced, queries with multple changes and running the data flow for the final…
-
Pulling numbers as DECIMAL over SQL
I'm migrating cards to some new DataSets and am checking for conflicts in advance. We've been pushing Excel spreadsheets and will now be pulling from Postgres. Domo has three numeric types: LONG, DOUBLE, and DECIMAL. I cannot figure out how to pull data into DECIMAL. I care only because they come in as DOUBLE and Domo sees…
-
MySQL newbie question
I have a dataflow I've built in Magic ETL, and I want to add a new column using a conditional statement to return the value from one of 3 existing columns (doing this in Beast Mode prevents me from getting the correct aggregations, so I'm trying to add in the dataflow itself). So I want to have Invoice Sales returned if…
-
Connect to SQL Server using impersonation
Is there any documentation on how to actually connect to a sql server with workbench? I have set up impersonation and used the connection builder. I am stuck and cannot find any information...maybe someone can point me to documentation on how to use the impersonation feature? Using SQL Server Native Client 11.0 (64-bit)…
-
Changing a string to a number
Hi all, I have a column that has values like this '14%'. Domo is considering it a string, but I'd like it to number, preferably a decimal. I've been trying a few different SQL functions in Dataflows to chop off the percent sign (and then CAST it into a decimal), but the ones that would do the trick don't seem to be…
-
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…