-
Calculate Previous Quarter Start and End Date
Hello! I am looking to calculate the Previous Quarter Start/End Date and the Previous Two Quarter Start/End Date. For example: Input: November 8, 2022 Desired Output: Previous Quarter Start Date - July 1, 2022 Previous Quarter End Date - September 30, 2022 Previous Two Quarter Start Date - April 1, 2022 Previous Two…
-
3 month moving with current month for a date column
I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR. I know the current month function to get only the current month moving but having trouble…
-
Equivalent of IFERROR(VLOOKUP,IF(RIGHT(- in Magic ETL
Hi Guys, Hope all is good. I am using Magic ETL and trying to create a flow replicating the current logic of my Excel file. In Excel I have 2 datasets. On the Main sheet a column is categorizing using the formula =IFERROR(VLOOKUP(value,range,exact Match),IF(RIGHT(Category,2)="AB","Above","Below)) Now in Magic ETL data…
-
Dataset query fails when not using a where clause
I'm trying to get all the data from my dataset by executing a simple query: Here's what my code looks like: rows = cnxn.execute(sqlcmd)for row in rows: dataset_id = '609f128a-ac54-4f4f-a71e-81362f9fa154' table_name = 'orders' query_url = "https://api.domo.com/v1/datasets/query/execute/" + dataset_id myobj = {"sql": "SELECT…
-
Is there a way to run ad-hoc SQL queries against arbitrary PostgreSQL tables?
My company is migrating from Mode Analytics to Domo, and I'm trying to understand whether and how I can run ad-hoc SQL queries against arbitrary tables. As a software engineer, I regularly use Mode's [query editor](https://mode.com/help/articles/querying-data/) to gain insights into our PostgreSQL database data in…
-
Making a bucket list of defects
I am trying to make a bucket for sentence long defects and put them in a column that is easier to filter by and group the defects into a certain defect code. I was able to get this working on a beastmode perfectly but when I try to add this in my ETL it gives me the code Failed to turn value string into Boolean. Here is…
-
Aggregate count function does not work in the denominator
When I use the following statement in a card summary, it works properly. But when use it as a value in a Single Value card, the count function doesn't work anymore: SUM(CASE WHEN ROUND(100*`Score`/`PossibleScore`)>=60 THEN 1 ELSE 0 END) / COUNT( `AttemptNumber`) For more context, the Attemptnumber column has 450 rows where…
-
MySQL Connector vs MySQL Partition Connector "Near Realtime" Performance Best Practice?
Hi all, I'm currently investigating the best practice for performance connecting with MySQL for a "near realtime" connection for monitoring missed call tickets in Domo. The idea is Domo will replace both FiveTran and Snowflake. Any guidance is highly appreciated!
-
Calculating hits per minute by timestamp and Parts ran
I am trying to calculate a live hits per minute by machine based on total parts ran per the difference between the start time of the day and the most recent timestamp. I am having trouble piecing together the correct formula to go about this. Does anyone have any suggestions? Thanks
-
Help with Converting Time Zone - 'DateTimeOffset' to 'DateTime' in SQL Connector
Hey everyone, I'm having trouble with a conversion from DateTimeOffset to Datetime in a SQL Query (inside a SQL connector). I am trying to convert my date columns when they are imported in DOMO as UTC so they are coming in as the right time (Db is UTC, Domo is Pacific) and convert them to DateTime. When I run…
-
Syntax for null
Hi all, I am trying to create a formula in ETL that deals with the null values in multiple columns. So far, no matter what kind of formula I use, I am unable to get the blanks filled in Here is what I am trying right now based off of all of the other posts I have read Again, nothing is turning up XYX like I would expect. I…
-
Magic ETL Help
Hi, I am going through the Data specialist practical test and am running into troubles. Here are my instructions: Up to this point, this is what I have done: Because I am not an admin on my company network, for those familiar to this test, I was unable to complete the join on the workbench due to accessibility issues.…
-
MySQL support for federated datasets
I would love to see federated datasets supported as inputs for MySQL dataflows. Many of my customers want to copy/paste existing SQL into dataflows, and since Magic does not currently allow that, MySQL is their next best option. My organization uses Domo Everywhere to publish federated datasets and stock content to…
-
Trying to Find the Top 10 part numbers by $'s for the year for each plant department.
I have a large table showing plant department and their part numbers with the quantity scraped / dollar amount scraped as the values. Is there a way to only show the top 10 part numbers by department in dollars? Trying to be able to always keep track of what top part numbers are being scraped. Thanks
-
Status tracking for hot board
I am wanting to track a hot board status for part numbers and want to use the status to track through the process. Is there a formula or beastmode that can remove the in process data once the ticket has been fulfilled and reads 'completed'. For example… Once the part number 3333-000 by Robert Storey was completed can the…
-
MySQL Join Optimisation - 2 hours, for what should take seconds!!
Hi, I have been trying to optimise a MySQL script, which for some reason is taking over 2 hours to run! I've been unable to figure out why the script takes so long to run, so any help and tips for identifying the cause of the problem would be appreciated. The script is very simple, essentially: SELECT x.*, y.field1,…
-
Is there a better way to perform a variable-length recursive query using MySQL ver. 5.6?
I'm looking to perform a recursive query on a data hierarchy with an unknown amount of levels. I've found that making use of stored procedures (or simple self-joins if the there are very few levels) works well enough most of the time, but it can be messy and time-consuming. From what I understand, Common Table Expressions…
-
Add Data that has been modified in the last 7 days
I have a snowflake dataset that has a modified date column. I want to merge the data that has been modified in the last 7 days to the dataset. Sometimes an order is modified after its placed and we want to capture the new values of the order. I have used this query but its not working SELECT * FROM "TABLE" WHERE…
-
How can I use the MATCH() function with a declared variable string in a MySQL dataflow?
I am looking for assistance in determining how to use the value of a string variable to DECLARE a persistent variable AS a STRING that can be passed into the MATCH() function in MySQL transform.
-
Can someone give me DDX Bricks examples which use traditional SQL (using JOIN) to query?
I want to use traditional SQL to query to fetch data from database in ddx bricks. My sql query contains INNER JOIN. Can it be possible? If yes, please refer to some existing example ddx cards or give me some example where using join and access result parameters from array-of-objects format are explained. Thanks in advance.
-
Need help reviewing sql query used to create domo sql dataflow
Hello, I am working on moving one of our SQL Queries to Domo. This query looks for users who have made a purchase in a month and no purchases in any month before or after. I was able to move this query but I am getting slightly different results. Since dates are used differently in both queries I wonder if I made a mistake…
-
Generate Date Range Column from Two Date Columns
Hi all ! Im trying to generate a date column with a range of dates from two columns: Table I have (date format (mm/dd/yyyy) ) Id start_date end_date field1 1 01/01/2019 07/01/2019 text1 1 07/01/2019 12/01/2019 text2 2 01/01/2019 02/01/2019 text3 The idea is to generate a list of dates between start and end date with an…
-
Deduplicating a table based on the content of certain columns
I have a dataset that has about 20 columns in it. The first column contains ID numbers and a lot of the ID numbers are duplicated multiple times. All the other data in the columns are also duplicated multiple times except one column named last_updated. The last_updated column lists a date. I would like to de-dupe this…
-
Using a View as a input dataset to mysql dataflow
Hi All, I am trying to have (a view) as one of the inputs to the mysql dataflow...But its not being accepted ...Can you please help me with this... Thanks, Arun
-
Creating a join from a lookup table
I have created a dataflow that joins two sets of data together, however when i join them together it's dropping data because it doesn't have anything to match with. How would i create a dataflow that would match all the data and any of the unmatched records would also stay?
-
LISTAGG or equivalent during data import from NetSuite
What SQL should I be using during an import to collapse data from a column into ',' delimited strings? when I use: listagg(item.NAME,',') "ITEM(S)", I get the error: [NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# l27jpdhw1857xj1xsmvi0[400] with just item.NAME…
-
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…