-
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…