-
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…
-
Creating indexes in mysql data flows
When I'm working on a mysql data flow, and create an explicit index on a table such as... CREATE INDEX mr_table_mti_index ON mr_table(mr_table_id); Do I then need to include an "ANALYZE TABLE mr_table;" transform or does mysql analyze the table automatically as part of declaring the index? I've done some tests and it looks…
-
Time Differential
I'm trying to calculate how much time on average an employee spends at a location. We have a clock in and clock out time. Currently, I have the forumula set up as: SEC_TO_TIME(Avg(TIME_TO_SEC(`Time Out`) - TIME_TO_SEC(`Time In`))) This works fine for almost everything I need. However, if an employee clocks in between…
-
Question - Dynamic report based on sales ranking
We have 12 main markets that we sell - I'm looking for a way that I can display the top 3 clients fo reach market + Others dynamically. For example, if client 1 is a top 3 client for Japan, their sales will show in Japan as "Client 1" but if they are not top 3 in China the client will be bucketed into "Others" Any ideas on…
-
Joining two data sets on a monthly aggregate
I am looking to combine two datasets one is a sales volume the other is an instance where we receive a claim. These are done on the day level in my data sets and I want to be able to compare them by monthly aggregates so that I can compare sales volume vs claims by month. When I try to join these two sets in an ETL it…
-
How to calculate the number of minutes in a month?
Does anyone know how to calculate the number of minutes in a month? I need to calculate the number of minutes per month to deduct from them idle time also in minutes and get the percentage of accessibility of the environment. Regards, Rodion
-
Mysql Query Field not showing in Blend
I have a mysql connection and field called UsageEndDate. For some reason this field does not show up in my blend. What am I doing wrong?
-
I would like to create a column segmenting the contents of another column into one of two classes.
I would like to create a column segmenting the contents of another column into one of two classes. The column contents either begins with "o" or "w". What would be the best approach to create another column classifying the "o" as "site one" "w" as "site two"? Thanks
-
Value Mapper using String
Can I use value mapper using string data? I want to label social media posts based on the caption to specify what products are they talking about. For instance So I want to search for "ball" in the caption and put "ball in a new column. Same goes for "food" in the second row. Any advice is highly appreciated.
-
How to Create Multiple Summary Numbers
Issue/Business Problem Description Many customers want to display multiple card summary stats that can give them a better picture of what’s going on in the card as it helps tell the story and can highlight what the card has in it. Description of Solution A customized Beast Mode calculation is necessary. The “secret sauce”…