-
Trying to filter out rows, where a certain column has NULL values
Hi, I have a dataset of almost 2 billion rows. I need to filter out rows, where a certain column has NULL values. What is the best and fastest way to do it? I tried creating a DataFlow using the mySQL option, but the flow has been running for more than 24 hours now. Could any of you please help in understanding how this…
-
Beast mode where the rank of the rows in a Dashboard can be introduced.
Is there a way to create a column in a Dashboard with sequential row numbers.? I am trying to repo the experience which exist in Excel. You update one cell with say '1' and then drag it down for all the rows. Filtering then is easy. For numbers of rows based on any sorting on a column in the result set. Thanks and Regards,…
-
Self Join Outer JOIN not working as expected
Hi, I am trying to do Outer Join on the same table. However I am not getting the result that is expected. I have the following dataset: Item Country Event Date Event Event Order A US Apr 29, 2019 Out Of Stock 1 A US May 13, 2019 Back In Stock 2 A US Oct 14, 2019 Out Of Stock 3 A UK Jan 28, 2019 Out of Stock 1 A UK Feb 04,…
-
How to transform from Excel Sumif formula to Domo ?
Hi, I have a data report with a template that looks like this in Excel: And I need to transform this into a card. So far I managed to make beast mode like this: Sum( CASE WHEN CONCAT( product_code, "_", account_id, account_name) = account THEN Value ELSE 0 END) AS 'Cost' But how do I take into account that the value for…
-
Summing column values based on dates
I'm trying to sum values as they fall within a range of dates. I think I have to do this in Beast Mode, because the Window function in ETL doesn't seem to allow for this though I have never successfully used it. I am not proficient in MySQL. I want to sum the 'EDP Visits' between Sundays. In the table below, the values for…
-
Case Statement with AND function in SQL
I am using below query to mark the two fileds as 'Exclude' SELECT *, CASE WHEN `account_number` = 20160001 and `document_number` = 57290 and `distribution_date` = '12/29/2018' then 'exclude' WHEN `account_number` = 40200001 and `document_number` = 57290 and `distribution_date` = '12/29/2018' then 'exclude' else 'Include'…
-
combining multiple rows into one
I have a table called continuity with for a specific order can have a driver, tractor, and trailer tied to that order, I'm trying to bring the data in by filtering my rows whether the type is tractor, trailer, ord driver then appending the rows together again, but nothing is coming up...could someone please provide some…
-
Filter to Last 12 Months (dynamic)
I've got two data sets I am trying to combine in Magic ETL to calculate inventory turns. In the sales data I have 2 1/2 years of history that updates weekly. In the ETL in preparing the sales data I need to filter to just the last 12 months (from last date in the set). Since this last date updates each week I'd like to…
-
Date minus a few weeks
Hello guys, Am looking for code which states: Return X (actuals) between (current date minus 3 weeks) and (current date minus 2 weeks). The code I am currently writing is not returning the correct values for the correct dates. Could someone help me out please? Many thanks to the community,
-
can i use the between operator in a case statement in a beastmode
I'm trying to write a CASE() statement in BeastMode that is similare to using the BETWEEN operator in SQL. I've tried this but BM will not validate. ( CASE when `FieldValue` <= -16 then 'Bad' when `FieldValue` > 0 then 'OK' when `FieldValue` between -1 and -15 then 'Good' else 'Ugly' end ) I've also tried this way. BM will…
-
How do I delete records in an ETL data flow?
Good Day, I have an ETL data flow that is recursive in nature updating Google Analytics data on a daily basis. A subset of data was missing so I appended another dataset in the flow which was ultimately in error. I am attempting to delete the data which was ultimately in error and replace it with new data. I'm not sure how…
-
Help for nested case statement in beast mode
I have these two measures made in beast mode that looks like this: Status CASE WHEN `Delivery` = 'Done' THEN 'Complete' ELSE CASE WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) THEN 'Pending' ELSE 'Complete END Completion Percentage ( COUNT(DISTINCT CASE WHEN `Delivery` = 'Done' THEN CONCAT(`Company Code`,…
-
how to use the percentage of a category as the summary number
Hello, I have this card that looks like this (Showing only one month's data) What I want to show in the summary number is the percentage of expired category(14,131 / 8,961+14,131) that is made available when you hover over the chart (61.19%). How do I do that for the summary number? Thanks!
-
Beast Mode assistance (issues w/month-over-month calculations for 12 month table)
This is a followup to a thread from last month (Table, compare several month-over-month). We've created a table card that for the most part function as requested, but noticed a few things that need to be accounted for and we haven't been able to figure those out at this time. In the screenshot below, we have a…
-
Creating an arbitrary date field unrelated to data to hold every date that exists
I'm trying to recreate a chart that compares Created to Resolved issues (based off JIRA data). How it looks in JIRA below Where i'm running into trouble in Domo is that I think JIRA just creates a random new date field/range on the fly that isn't included with the data when pulled over. In Domo I have to choose what field…
-
How do I translate these report format in Excel to Domo?
Hi, I have these tables that looks like below: The formula shown is for J12, and going from J12:V16 they have the same syntax of formula like that. Other numbers are manually input or linked from other sheets. Now, how do I replicate the report format and the formula in Domo? I have tried using Beast mode to solve this,…
-
Splitting comma delimited keywords and combining in one column
Use case: For every account I have a set of comma delimited keywords stored in 1 column (think "SaaS,business,b2b,etc"). I ultimately want to create a list of all unique keywords, then compare that against my dataset to measure frequency ("How many accounts contain "SaaS"?) My feeble attempt: I've managed to split the…
-
Calculating hour differential over multiple days
I'm trying to calculate the number of hours elapsed between when a given event occurred and the current time. Both are formatted as "DD MMM YYYY HH:MM: SS". Basically, if the event occurred at 01 Jan 2019 12:30:00, and the current date is 03 Jan 2019 12:00:00, I would like the output to be 47:30:00 Thanks!
-
SUM of CASE in Beast Mode
Hello, I would like to create a beast mode which can give me a % of a column in a table (e.g. % OCJ Actual) like such: Marketing IntentFY GoalOCJ Actual% OCJ ActualCOLLABORATION5,000,000500,00010%SECURITY5,000,000500,00010% The beast mode formula seems correct but I am getting this error 'An issue has occurred during…
-
How do we data mask in DOMO ?
I need to data mask some of the data that is coming from SFDC, successfactor. Do we have any automated function in domo we can do with ?
-
Beast Mode - Parent of Parent type join
I have a data set of project items at multiple levels of a project hierarchy: project > task > sub task. I'm trying to build a sort of rollup card that shows the items based on parent link relationships. i.e.: * Project Parent = Self * Task Parent = Project * Sub Task Parent = Task I'd like to be able to have a column that…
-
Period over Period card, Last 'n' weeks compared to 52/53 weeks ago = How to EXCLUDE current week?
I have a BigQuery dataset that updates daily, but is one day behind on purpose. Today is Monday, so the lastest data that I have is from Sunday. My company is working on a Mon-Sun fiscal calendar, and, yes, it IS optioned for our instance. I am creating a "Last 5 Weeks" grouped bar card, with comparison enabled to 52 weeks…
-
Columns with 2 Unique Values (1,0), into one Column
Hoping someone can help me solve this. I have a dataset that has multiple columns with a 0 or 1 value. I'm trying to combine these multiple columns into one using a beastmode, naming those values with their respective headers - So: (CASE when `Business Change` = 1 then 'Business Change' when `Division Change` = 1 then…
-
Incorrect Column Type Preventing ETL
I'm trying to use Magic ETL to transform a dataset using rank&window and remove duplicates. My ETL keeps failing due to the error "Failed to parse data as integer for column". I attempted to input a "Set Column Type" step connected to input but the ETL is failing at the "Input Data Set" stage and never reaches that point.…
-
Can DOMO add functionality for certifying a dataset/dataflow in DOMO?
I've really enjoyed the new Certification process for cards. I've seen on other threads as well that it has been requested to expand that functionality to pages and collections which I agree would be very helpful. An additional thought/request I'd like to throw out there is the ability to certify a Dataflow or Dataset. It…
-
Finding the "Growth Rate" of a metric in Beastmode
We are currently building a dashboard where looking at change over time is critical - ideally how we'd like to accomplish this is by measuring the growth rate, or change in the metrics over the period set by the analyzer. Is it possible to calculate this in a beastmode while still retaining the ability to change the…
-
Net change of facebook followers in 30 day period
I am trying to make a calculated field which determines the net change in facebook followers in the last 30 days. The data has columns: exampleDate: November 04, 2018 1:00:00 AMTotal Page Fans: 262 How can I determine the number of fans 30 days ago, and yesterday and output the difference for my card? I am very amateur at…
-
How to show week-by-week comparisons in grouped vertical bar cards where the weeks roll-over years?
When I got into work this morning, the Grouped Vertical Bar cards which I had built to show week-by-week comparisons of THIS year's values versus LAST year's values were all broken. The Beast Modes that I had created to differentiate by week were all malfunctioning because today is Wednesday, January 2, 2019, and the end…
-
Nested UPDATE statement not populating column
I am trying to update a dataflow with MySQL. I have a report being pulled into DOMO from Salesforce and I altered it as follows: * connected Salesforce report to DOMO * Used ETL to filter the data into five buckets. (Marketing Channels) * Created a new column: 'Sub Channel' - value: NULL * Output as its own ETL dataflow I…
-
number of days in a month
I have a monthly amount that I need to spread equally over the number of days in the month. The data looks like this: Month Value 01-Jan-2012 100000 01-Feb-2012 121002 01-Mar-2012 123123 01-Apr-2012 118239 I have to spread the Jan amount over 31 days, the Feb amount over 29 days and the March amount over 31 days. So I can…