-
Calculate rank so that the smaller number is higher rank
I need to calculate the rank on a metric where the lower value is higher in rank, and the higher values are lower in rank. This is how I've been calculating standard rank (where higher values are high in rank) SELECT `Metric` ,`TimeFrame` ,`VALUE` ,`Name` ,`Grouping` ,`ProcessorName` ,CASE WHEN @priorMetric = `MetricID`…
-
How to use Escape characters in Beast Modes?
Hi, I am trying to create a web link for a card using a beast mode and some of the values that will be passed through the beast mode contain apostrophes ' . When they are passed through the beast mode the link is broken because the value is cut off early. For example the value 'Square's Lake' is cut off early and ends up…
-
Is AS valid in Beast mode?
Good afternoon, Can AS be used in Beast Modes to create aliases? It doesn't seem like it but I might also be super bad with SQL. Thanks! -Joe
-
Rolling Average for Each Category
I have a dataset containing total monthly units by month and by person. I'd like to calculate the 3 month rolling average for each person. How should I group the data so that the rolling average calculation goes by person and not by date?
-
has anyone had any luck creating dynamic columns in mysql
what I'm trying to do is for sql to check and see if a column missing, if it is then i want sql to create the column. I've done several attempts with no luck. the alter table add column sql works but it fails when i add it to the if condition. any thoughts? CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT * FROM…
-
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…
-
Moving Values One Row Down - Conversion Rates
Hello! Does anyone know how to move the "Stage Count" column down one row (Stage Count 2) rather than erasing the top value? I am trying to get the Stage Count 2 column to read: blank 3821 336 122 33 32 I attempted a LAG, but this version of mysql is too old to use I believe :( Thanks, Keirsten
-
Can I write SQL directly to my data sources?
I know I can click on "SQL" in the top ribbon to write SQL against my data sources using a data flow. However, does Domo provide a direct option? Many of my users just want to open an interface to write SQL and then preview and download results.
-
Rolling 30 Day Formula in magic ETL
Hi, I am trying to write the formula above within magic ETL. Column A consists of static values. Column B starts with 0 as the first value and has the formula shown in the image. The formula resets its count every time it gets to a number greater than or equal to 30. The only way I can find to do this is with a LAG()…
-
Do MySQL dataflows support the use of the WITH clause?
I'm trying to use a WITH clause as outlined here: https://dev.mysql.com/doc/refman/8.0/en/with.html in one of my MySQL dataflows. But I keep getting this error: "The database reported a syntax error. You have an error in your SQL syntax" My (very simple) code is: WITH test AS (SELECT`EmaiName` FROM transform_data_2) SELECT…
-
MySQL Protocol Version Error
I'm trying to connect to a MySQL Account. When I enter in my credentials and CA Cert I get the error "Failed to authenticate. Verify the credentials and try again. Error setting up SQL connection. Could not connect to: Received fatal alert: protocol_version" I've verified the user is using TSL v 1.2. I'm also able to…
-
MYSQL - Trouble adding new column via LEFT JOIN
Hello! I am having trouble adding a new column, "Dummy", to my output dataset. Although the query is valid, the output does not produce the "Dummy" column when ran. I am trying to identify rows to remove based on the criteria defined in the LEFT JOIN. See below. SELECT A.`Posting Title`, A.`Weekly Reporting Date`,…
-
Schema not keeping changes, but no error given - have you run into this?
Hello, So I have set up a job using MySQL ODBC - executed, all good. Job T Then set up another job with a local CSV file - executed all good. Job A So then I want to update Job T to do a lookup transform of the results from Job A. This seems to run well. But I also need to update the schema. I need to remove a column and I…
-
'Incorrect column name' doing a Dynamic Pivot
Hi there! I've been following this tutorial for dynamic pivot and so far it has worked: https://domohelp.domo.com/hc/en-us/articles/360042923454-Dynamic-Pivot. However, I get this error: I thought it was because the name was too long, but the character limit is 1024 and even performing a substring I got the same error. 😓…
-
How to Create Monthly Averages for Headcount?
Hi All, I'm in the process of building an attrition model in Domo in the form: Total Terms / Average headcount Terms is easy, that's just a count of everyone with a term status for that time period. Headcount is where I'm getting stuck. My headcount numbers are currently aggregated based on headcount at the end of each…
-
use fields from different tables group by date to calculate percentage
Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation. Table a looks like this: Date All Revenue 09/01/21 100 09/02/21 200 Table b looks like this: Date HDM Revenue 09/01/21 90 09/02/21 170 I need to build a table that would calculate the percentage of HDM Revenue,…
-
Need help to connect a Microsoft SQL server to DOMO
How can I connect MS SQL server with Windows authentication mode only? Should I ask the server owner to change the selection from Windows Authentication mode to SQL Server and Windows Authentication mode?
-
Problem with SQL for WordCloud Chart
Hi all! So, I am trying to run SQL code using these instructions https://domohelp.domo.com/hc/en-us/articles/360042925094-Word-Cloud-Chart When I am at step 9 and trying to run this code CALL word_cloud('nuvaira_patientlogreport_recursive', '`Notes`' , 'y','y' ); It gives me an error 'Invalid. Whoops! Something went…
-
Remotely trigger a DataSet refresh?
I'm checking out some of our options for speeding up data ingestion in Domo. A few of our Postgres pulls are taking longer than we would like, and I'm exploring creating a materialized view to cut the PG processing time down to 0, from Domo's perspective. That still leaves the data transfer time, and the Domo processing…
-
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…
-
Can you prevent the MySQL connector from locking its source table while syncing?
I have a large table that takes a while to sync over to Domo through the MySQL connector. I currently schedule the sync to occur overnight because a table lock prevents other users from reading the source table. I would like to sync the table on demand during working hours though. Is there a way to instruct the MySQL…
-
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…
-
Workbench ODBC - Dynamic query/parameter
When we are establishing a new job in Domo Workbench of the type ODBC, we write the query in the box of Database Query Editor as in the image below. However the query we want to do should not be static and we would like to modify that query based on external triggers. For example, in the image, is it possible to pass a…
-
MySQL Connector to Read Only Some Tables in a Database rather than All Tables in a Database
We are using a MySQL connector to load data from our Google Cloud SQL servers into DOMO. From what I can tell, it is only possible to connect to a Database, and all the tables within that database become accessible via the connector. Any of the tables in our database can be chosen in this window (see the highlighted…
-
Beast mode to kill div0 errors
Hi, can anyone tell me why If my "A" code works, and my "B" CASE WHEN... THEN 0 ELSE 2 END code works, why can't I substitute code "A" for "2" in code B? I'm trying to kill divide by zero errors in a beast mode. --A SUM(IFNULL(`qty sold instock`,0))/SUM(IFNULL(`qty found`,0)) --B (CASE WHEN (IFNULL(`qty found`,0)) = 0 THEN…
-
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…
-
Summing up filtred values based on criteria
Hey guys, I will try to explain my issue, I'm really loosing hair on this one, hope wil get feedback from you
-
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…
-
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…