-
Business days between date function
I use the datediff for business days function in a number of mysql data flows and it works great. However, I'm getting some very strange results on the latest flow that I'm working on and I can't figure out what's going wrong. I have a start date which I'm looking to compare to the current date. However, the code I'm using…
-
How do i trim out an email address from a field with a string?
How do i trim out an email address from a field with a string? Example of Field: Yes, we accept this current pricing and would like to proceed with this order. Thank you, Bob Dole Lab Research Analyst Poss Lab—Dole University bob.dole@some.edu Thanks
-
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 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…
-
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…
-
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`…
-
Extract 1 row / few rows from big dataset (>100M rows)
When I try to extract the _BATCH_LAST_RUN_ from a MySQL dataset with 106M rows, the ETL takes 3h20min. Is there a way to extract only one row from the dataset. For the moment, I have to wait until the full 106M rows dataset is load in both Magic ETL and SQL.
-
Historical Database I inherited
Hi all. I'm dealing with CRM data, and have a snapshot of all of our data that is appended to a full historical daily database. This is done in a SQL transform, but I am completely unable to figure out why the input data is being appended to the output instead of clearing it. I am completely dumbfounded. I'm attaching…
-
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…
-
2 Issues: MySQL SSH - Query Parameter and STREAM API
Sorry for combo post but I got problems. 1. I'm using the MySQL SSH Connector - I'm using the QUERY option to pull in data from a table. Currently, most are just daily imports but some tables are 1B rows and so doing a 'Select * from table;' is VERY inefficient. What I'd like to do is using a parameter to only pull in the…
-
How to create a backlog trend card?
I'm trying to create a card that shows the trend of case completion against a backlog each month (based on open/close dates). Ideally, it would be a multi-line chart with 'month-yr' on the X-axis and COUNT of cases on the Y-axis, for the following series: * New = COUNT of cases opened during month * Closed = COUNT of cases…
-
Trying to filter out rows, where a certain column has NULL values
Hi, I have a dataset of almost 2 billion rows. I need to filter out rows, where a certain column has NULL values. What is the best and fastest way to do it? I tried creating a DataFlow using the mySQL option, but the flow has been running for more than 24 hours now. Could any of you please help in understanding how this…
-
Add group by as an additional field
Hi everyone, I am looking to add a column to a dataset where I join all my users and their orders (could be 1 or more) and get a count of how many orders an individual user has placed. I pulled all the columns I want in my ETL dataflow, and then calculated this field using the "Group By" transformation. I group by…
-
Automating Multiple Imports
I am very new at using workbench so sorry if this question is too stupid to even consider. Essentially I want to pull a report for a partition of data. For example select * from tables where GroupType = somenumber Instead of creating a new job for every iteration of somenumber I was wondering if I can have Workbench…
-
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…
-
Domo Connector IP address
Hi, Can anyone please provide the IP addresses of the Domo Connector, so that we can add the rule in our firewall to access MS SQL database.
-
Line Breaks Stripped from mySQL Text Fields
Using the mySQL SSH connector, my text fields (VARCHAR and TEXT) are getting line breaks stripped, making all my text appear as a single line, even though the source data has line breaks. I've tried using a BLOB field, as well, but those don't seem to work at all. Is anyone else experiencing this? Is there any way…