-
Group by Value in Column
Hi All, I'm looking to find a way to group a dataset based on a specific value in a column. Any ideas how to do this? Thanks! Eric
-
How to apply mean to all rows though beastmode?
Is there a way display the mean for all rows without aggregating (summing)? This would require aggregating a field and returning it to each row. For example: ID,Amount,Avg Amount 1,$10,$20 2,$20,$20 3,$30,$20 While this can be done in dataflow, it needs to be dynamic (use will filter and change date range) and thus done in…
-
Is a Case Statement using COUNT / SUM possible?
Good morning. Not sure if it's the case statement itself, i.e. incorrect syntax or if there's an issue related to divide a COUNT by a SUM value. We've also tried to run this as COUNT / COUNT but to no avail. We'd like to use only certain agents when trying to determine a Completed Conversion % for offers. The beast mode…
-
Creating New DataFlow from Zips2Fips DataSet and ODBC DataSet
I have USA map card that I want to drill down to counties. I got Zips2Fips DataSet from DomoSolutions that I have to join to my DataSet. I tried to do that through Magic ETL joining these two DataSets matching ZipCodes, but I am getting empty rows. What could be the problem? Thank you, Marko.
-
HTML - Mobile and PPT friendly
Is there any trick to getting the HTML that I've put into summary numbers via beast mode to work on the mobile app and/or in a downloaded PPT of the card? I'm thinking the answer is no, but just wanted to check. Not a huge deal, but having such nice looking summary numbers on a desktop browser then it looking messy on a…
-
Finding the last non-zero value in each column
I've got a DataSet with several columsn. Each column stores a single statistic: TotalItems NewItems RepairItems There are many rows, each of which has each column along with data, location name and other values of interest. Here's the question: Sometimes rows have 0's that indicate 'no change'. What we're after is a chart…
-
Case statement clarification
This seems pretty straightfoward, but I wasn't able to find a definitive answer searching Domo or Google. We're hoping to clarify whether the beast mode formula (case statement using "OR") will return a total count of five (5), i.e. count each row one time or eleven (11), i.e. instead counting EACH instance of a condition…
-
Error: Table in Dataflow "doesn't exist" but it does
I have two transforms with output tables that I select from to create a new dataset however the system tries to tell me the table doesn't exist and I'm not sure why. Is there some sort of limitation that I'm not understanding?
-
Exclude all data from Current Month (in either Beast Mode or ETL)
My aim is to exclude all current month data. I have tried to do this in ETL, however the date field filter doesn't appear to allow for "less than current month" - happy to take suggestions. I have also tried in Beast Mode formula. I have successfully excluded data in other calculations using currentdate however am…
-
When viewing a dataset, is it possibl to see a list of flows and cards that use it?
When viewing a dataset, is it possibl to see a list of flows and cards that use it? Currently, I can only see the count of the number of cards/ETLs that use it
-
Magic ETL Help - Overwrite value in a column if another column equals one of two values
In our sales system there was a recent change to a part number, and I need to correct history in the dataset. If the 2nd Item Number is either "LOT SQD STOCK" or "LOT SQD SPECIAL" then I want to overwrite the value in the Supplier ID column to be "SQD" otherwise I want the Supplier ID column keep it's current value. I'd…
-
Average if channel type statement beast mode
Hello, I am trying to create a summary number that takes an average of one customer type and compares it to another. My code currently looks like CONCAT( (CASE when `Channel` = 'RET' then SUM(`ordercases`)),' | ' (CASE when `Channel` = 'FSV' then SUM(`ordercases`)) I am having trouble with the syntax. Has anyone had any…
-
Business Days
Hello all! I noticed a post or two discussing that there was a Business Days or Workdays-type function in beta and I am wondering if there are any solutions to finding the number of business days between two dates until this solution goes live? My data is fairly straight forward; assuming two columns [Date 1] and [Date 2]…
-
Count Based on Unique Identifier
Hello all, I'm very new to Domo and in particular Beastmode. In this case, I do not have access to the dataflow behind the chart. I'm trying to count the number of yes and no responses for each unique ID number to put into a stacked bar chart. My X-Axis is the person and the Y Axis is the total number of unique items…
-
Date Difference contingent on other column values SQL
Hi I am trying to find the date difference between different rows with like IDs. Please see screenshot of Excel to see data needs clearly. Also screen shots attached from SQL transforms. SQL Does Validate: Transform 1 SELECT sp_palapprovals_workflow_history_dwb1. *, Case when (`Primary Item ID`=`Primary Item ID`) and…
-
Subquery versus Output Table
I am always looking to optimize my dataflows and was curious if it was better to have multiple output tables in a dataflow or less output tables by utilizing subqueries? For debugging and readability it is nice to have multiple output tables but I do not want to do this if it will have a negative impact on performance.…
-
Magic ETL - Add a "Flag" column based on comparing a date in one column to current date
In my data I have a date column. I'd like to create a column via Magic ETL (want to use in a filter) that looks at the date a determines if it is in the past or in the future. Here is a short video that explains. From an Excel formula standpoint, it would be: =IF([@[Last Update]]>TODAY(),"Future","Past") I tried with value…
-
Dealing with column types & bad data
Here's the problem I've come across a few times now: A CSV upload file has a column which looks like integers for the first hundred or so rows, but lurking further down are alpha characters. When creating a transform in magic, it loads the first 100 rows, sees they're all integers and sets the column type as such. Then,…
-
Magic ELT Data Flow Unexpected Results
Hello, I have two input data set that were trasnformed in SQL. I have two columns in each data set. Column 1 is the part name, this column is the exact same in each data set, for example: row 1, in data set 1 & 2, contans the exact same text. The ladder is true for each row in each data set. The second column is the…
-
How to return first 100 rows of data?
My dataset is refreshing every day, putting the newest data at the top of the dataset. I only want to chart the first 100 rows of data, the most recent. What function would I use in Beast Mode?
-
How to remove special characters from cells and parse data in sql
I have data stored as text and am wanting to convert it to int with-in SQL. Currently the text reads (8'-6 11/16"). I am needing to remove the foot symbol ('), the dash (-), and the inches (") symbol. Then take each number (stored as text) and convert to int. At which point I would rebuild the cell to read 102 11/16". If…
-
Beast Mode Case Statement Help
For some reason this CASE statement will not validate. I have all the required parts. `PayTotal` and `PayTargetWeek` are both number values and I want this field to return a character string. case when (sum(`PayTotal`) / `PayTargetWeek`) <= -.05 then 'Under Target' when (sum(`PayTotal`) / `PayTargetWeek`) >= -.049 and <=…
-
Exclude data (COUNT) that doesn't meet a predetermined min/max # value
We're trying to determine the number of customers that have to call more than a certain number of times in order to have their concern/issue resolved, i.e. those who are not able to receive first call resolution. Domo obviously can determine the number of times a certain phone# has called in, and we've generated a card…
-
full outer join in mysql
I noticed that "full outer join" is not working in MySQL. Only Left Outer Join & Right Outer Join can work. Can someone help me achieve "full outer join" using MySQL? Do I have to switch to RedShift for that? Thanks, Hua
-
DOMO Time Import Problems
I have an Excel data file with the following information: Date of incident | Time of Incident 12/27/2017 | 01:00 PM The literal value in the time field is "01:00 PM", verified by converting to .CSV and opening in text editor. However, when DOMO imports the time field, it correcly assigns it as a time data type, however it…
-
Performance Issue MySQL DataFlow
Hi there! I have a bit of an performance issue. I am basically trying to create a table for our sales department, which includes all sales countries (as a filter), sales towns (column 1) and sales (Total) from this year (column 2), last year sales (column 3) and a variance column (column 4). Furthermore I want to insert a…
-
Beast Mode, Number to Date
Currenlty would love some help finding out which Beast Mode (if any) can be used to convert a number into a Date field in Domo. Currenlty the number shows up as 170,913,000,000. This should translate to 2017/09/13 000:000. I really only need the 2017/09/13 though to appear. Any suggestions/help would be appreciated!
-
find max values
Hello, I have data that looks like this: a,1 a,2 a,3 b,1 b,2 c,1 c,2 c,3 c,4 d,1 e,1 f,1 f,2 I want to add a beast mode field so that the data in that field displays like this. In this way the beast mode field would return the highest value from counting the first two fields. a,1,- a,2,- a,3,3 b,1,- b,2,2 c,1,- c,2,- c,3,-…
-
Sum of Value based on specific date parameters
I have a Card that is in Table view and I'm trying to populate multiple columns that are all summing a value based on specific date parameters. The issue I'm having is trying to set the parameters to autopopulate based on Current date. For example I want the sum of flagged values which match certain date parameters, at the…
-
MySQL Data Flow - Running Total - Specific Period?
Hi there! I am currently working on a running total, which worked out fine. The only thing I can´t figure out is to calculate a running total per specific period. My table should look like that: As you can see our season starts on the first of May and ends on the last of April in the following year. My current code for YTD…