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