-
MYSQL ETL including parameters
Is there a way to create parameters in SQL statements within the MySQL ETL data flow? Here is an example: set @lastDate = select * from `last_date_of_spent_data`; -- this returns a single column and row Select `Program Name`, @lastDate as LastClosedPeriod
-
Combining Data Points after Split Column
Hello! I'm attempting to get a new column that shows flight dates/run dates. The original data is located in a Line Item column (screen 1) and after a Split Column action (screen 2), the flight dates/run dates end up split over several different columns because the # of _delimiters in the original column isn't the same.…
-
Python
Hey guys, I can't find any documentation on how to use the python widget. I've read the documentation on the site already. It didn't provide me any insight on actually implementing it and the Schema tab is far too confusing. I don't even know why I would import all my selected columns if I'm going to be creating another…
-
Magic ETL dataflow not running because of no change in data
Hello, So, I created a magic ETL dataflow that selects a row from a calendar where the shortdate=Current Date and I scheduled this dataflow to run at 6am daily. The dataflow has not run once becuase it states the following: Your DataFlow was last triggered on 4/3/2019 6:00 AM but there was no change in the data, so it…
-
Date Add Formula
I have no clue what I am doing wrong, please help! I am following the function description and it seems like I have the correct arguments but maybe I am overlooking something. I am just trying to add 1 month to the current month using a Formula tile in ETL 2.0.
-
using google cloud store postgres
I am told I need to put the IP address of DOMO onto the networking "connections" in my google cloud platform to allow DOMO to pull the data from my SQL database. Would anyone know what that approved IP address is for DOMO ?
-
Is it possible to switch a data set to a different dataflow?
I've got a team who uses the Excel plugin extensively, through which they query a particular dataset. However, this dataset is based on an ETL flow which I'd like to recreate in SQL so that we can iterate on it more quickly. Since they'd have to remake all of their query tables to be based on this new data set, I'm…
-
% of Total Sales Column Add
Hello All, New to Domo and appreciate the community support. I have a table card that includes net sales by cashier listed for the day in descending order. I would like to add a column that shows their sales as a % of total sales for the day. How would I do this ? Thanks !
-
Is there any way to do an UPSERT with data coming from Postgres?
Our main SQL database is Postgres, and we're populating DataSets with calls similar to select * from view_for_domo We're using full replacement, rather than incremental pulls. We're interested in using incremental updates rather than full refreshes of the DataSet to speed things up. After checking the options I can find in…
-
Converting Decimels Numbers into Hours, Minutes, Seconds
I currently have a column that has decimel numberic values and I need to convert these values into a time format. Please note that the column containing this data is not a Date/Time column. A beastmode or and ETL transformation will work for me. Example: Column contains 2.25, I would like for it to display as 2:15 or…
-
conditionally start a dataflow
I am aware of the 3 ways to start a dataflow...manually, on a schedule, and only when datasets are updated. I have a use case (which I suspect others may as well) where I only want a dataflow to run conditionally. Example: We are pulling multiple datasets in from an application that on occasion experiences data quality…
-
SUM with multiple conditions
Hello everyone, I am having trouble with a DOMO Beast mode sum Calculation. Below you will find my existing calculation. I am looking to add the value of all records that have a date to Customer in column A and exclude those records in column B that have a payment date. Essentially I am looking to calculate the value of…
-
How to get aggregated data without losing rows
I am looking to get count for each row without loosing any row. It can be achieved easily in excel using count if in excel but here how I can implement in ETL or Beast mode? Please see the sample input and output I am looking for in below image
-
variance
Performing the percentage variance calculation gives me this warning An issue has occurred during processing. We are unable to complete the request at this time. What can I do to solve it or what is it due to? Thank you
-
Adding Column to Calculate Difference Between Weeks
Hello, I am trying to add an extra column that will calculate the difference in order volumes from week to week. See attached screenshot for details: Week 1 -> Week 2 difference is 7394 Week 2 -> Week 3 difference is 53 Week 3 -> Week 4 difference is 452 Basically using the current week's number and calculating the…
-
Is there an alternative of distinct on for redshift?
Hey, I am trying to build a dataflow using redshift but distinct on is not supported. It's a simple query: (SELECT distinct on ("id") var_list.* from "indicator" as ind order by "id", "rank" desc) Any suggestions for workarounds?
-
Cumulative Count by Month
I am looking to obtain a count of all dates that are less than each month on a table. Please see attachment for reference. In other words, I need a cumulative count of how many enrollments the company has so far going into each month. The 2019-Dec line should count all dates less than 12/1/2019 The 2020-Jan line should…
-
Record count for prior month or prior quarter at start of the year
I have a Beast Mode that I've been using that was working fine up until January 1st. The issue is that I have a number of Beast Modes that are calculating record counts for Prior Month, Prior Quarter and Prior 2 Quarters past. They all seem to have trouble getting past the 2018 component, which I know I've coded in the…
-
How to build data and rankings for pivot tables in DOMO
Hi guys, looking to build week on week report, but not sure what type of DATA format I should pre-create to be able to build things that would show like in this image. The Rankings Column is the one I'm unsure if it is possible 1) adding a number 2) showing a calculation in the rest of cells Please advise
-
Calculate percent of whole
I have rows of client data with 2 fields `weeks` and `recommended_weeks`. I would simply like to know what percent of rows have `weeks` = `recommended_weeks`. For some reason I can't seem to make this work. I tried the following but it's showing me ALL rows, rather than an average. sum(case when `weeks` =…
-
Using PyDomo to get dataflow information
I can pull down the dataset list just find, but the metadata function doesn't return anything about the upstream dataflow id. Has anybody else been able to work with this and grab all of the information about the dataflows to see what is linked to what? We have a bunch that need to be audited and cleaned out and I can…
-
How come MySQL does not support window function, specifically lag in dataflows?
Hi, I am currently building a dataflow and trying to get the best out of both sql dialect. I use Mysql to order my data (because redshift doesnt order properly) and then use another datflow in the redshift dialect to be able to use window function. What I am curious about is to know why Mysql does not support window…
-
Reference data
I have multiple datasets and cards. In one dataset I have a beastmode where I calculate the Customer Lifetime Value. In another dataset I use the CLV but not as a reference as I don't have the data in this new dataset. I write the CLV hardcoded. Problem is that if the CLV changes I have to update the CLV manually in other…
-
Max Case Statement
Hello! I would like to create a beastmode that will calculate the case fill percentage for the most current fiscal week I have in my data. For example, if I have fiscal weeks 1-45 in my data, I would like the calculate the case fill percentage for week 45 only. Each week I will receive new data, so next week when I get…
-
Return AVG DayName
Hey I was wondering if there is a beastmode windows function that would allow me to return the avg dayname from the average of x? I have data that includes a domain, a column for day name(made via beastmode), and an avg of impressions). Any ideas? I thought the following would work if I added concat in front of it but I…
-
% of Total
Hey there, I'm trying to create a % of total column for my dataset with various domains. Issue is, there's multiple rows with the same values as others. Tried to perform a group by domain then rank by video plays with a partition by url, but it is not working. Thought maybe a window function could work, but I would need it…
-
Beast mode for rolling count of purchasing customers within a specified period month over month
I'm trying to develop a beast mode formula to help me derive count of customers who have purchased within the last 30, 60, and 90 days, which rolls month over month. For example, the count of patients who have purchased within 90 days prior to September, within 90 days prior to October, and within 90 days prior to…
-
Beast Mode Appears to only be Calculating First Part
I have a beast mode that calculates the number of units based off the Period (month) and how far into that month we currently are. The beast mode was working fine but once we got into Period 2 the total is only calculating the total for the first period. Something is causing it to stop and I cannot pinpoint what the cause…
-
When x is null and y and z is not null then case statement - multiple parameters
Hey there I'm trying to perform a case statement in magic etl using the formula widget. I'm trying to use automl tool to predict the likelihood of receiving a like, comment, and/or shares based on a section topic. I'm not sure if I'm on the right path though. (CASE WHEN `likes` IS NOT NULL AND `comments` OR `shares` IS…
-
Beast Mode duplicated in New Card
Hello, I'm relatively new in DOMO and while creating new cards off other existing cards the Beast Mode was duplicating itself (ie started with one Beast Mode and after 8 cards there are now 8 Beast Modes). This is an issue because when I change the Beast Mode I'm having to do the process on every card. How can I get the…