-
Excel text to column equivalent in magic ETL
What is the equivalent of the text to columns (from Excel) function in magic ETL? I have a column with mixed values that I need dileneated based on a constant character (in this case ";"). Ex. 123456;SKUName Need two columns Column A Column B 123456 SKUName
-
Domo Beast Mode/Data Flow
Hello, I currently have a column in my data set that is numerical called "session duration" with values ranging from 0 to 45,000 (in seconds). I want to be able to create a new column that does value mapping for values > 10 to be identified as "Greater than 10" and values < 10 to be identified as "Less than 10". Currently,…
-
Do I need to use Beast Mode to filter data in this way?
I'm working on a sales dashboard and I've got a scenario where within a card I need to filter out pitches sent to internal staff unless they are tagged with a specific word (of 10 possibles) contained in a measure. Is Beast Mode the solution and if so how would I do it? Any help much appreciated. Cheers, Danny
-
Beastmode calc for previous calendar month data
I am trying to create a beastmode calc for counting the number of records that occurred in the previous CALENDAR month. In current month (July), regardless of what day I run the report in July, I want my report to display the cound of records that occurred in calendar month June. So from Jun 1st to Jun 30th. I have tried…
-
Magic ETL Conditional Column
Hello, I am brand new to Domo and looking for the equivalent data transformation in Power BI that is called "Add Conditional Column" which basically allows you to write a case statement based on values in existing columns in your data set. Ex. If [Sales Rep] = "Dan" and [Order Type] = "1" then "Exclude" else "Include". Is…
-
Joining One-to-Many relationship datasets
I have two datasets that I am trying to join together, posts and metadata. I am figuring out how to do this via a pivot. The data schema looks like the following: # post schema ID | Name | Type 1 | Post 1 | post 2 | Post 2 | post # metadata schema Object UID | Field Name | Field Value post:1 | Number of Cats | 2 post:1 |…
-
Beast Mode works but not in Summary
I have created a beastmode calculation to show as my summary. The calculation works, however when I select it to show as a summary number it returns "No Summary Number". My beast mode is: CONCAT(ROUND(AVG(CASE when LOWER(`On-Time or Late`) = 'on-time' and LOWER(`Drop/Live`) = 'live' then ABS(`Live Turn Time Data`) when…
-
Total the count of Attendees for last 7 days & last 14 days in Table View
I am looking to find a way in Beast mode to do the following I have CustID and Class_Date and Class_Time. I am trying to show total Attendees and % of total for last 7 days and last 14 days both displaying in Table view mode. Any thoughts on how to do thiss via Beast Mode or any other way?
-
sum column values
I have a column total revenue which is calculated for each customer on a separate row, but I'm wanting to total alll the total revnue values together for all customers then divide each indivudal revenue by the toal to get a % per customer, is this possible? thanks
-
Connecting to Pardot in hopes of getting email stats
I've connect to Pardot in hopes of getting email stats. However I cannot get a 'send date' for my emails. There are a dozen different report types to pull, but none of them have ALL of the email data AND a send date. The report types that do have a send date do not have all of the open, clicks, bounces, yada, yada fields I…
-
Deliminator question
I want to count the information but don't want duplicates how can I do this and add the correct info for an accurate count? AHA.10133|1 AHA.101361799|1 AHA.10205|1 AHA.1012178447|1 AHA.10135|1 AHA.10135|3 AHA.10138|7 AHA.103352686|1 AHA.103352686|1 AHA.103352686|1 AHA.10135|1 AHA.10135|3 AHA.10138|7 Want this to equal 1…
-
Is it possible to have a Table chart type with one of the columns displaying a barcode of a value?
Hello, I have a rather archaic entry I have to do in one system based on DOMO data. I'd like to use the domo data to make a table of information where one of the columns displays in a bardoce font so the users can print the sheet and scan the barcode. The limited barcode experience I have is that the barcodes I need are…
-
Collapse and Merge Columns
Hi, I'm trying to join two datasets together with a single shared field. I can do this just fine, but I'd like to collapse one column from both datasets into the same column and leave the rest of the columns intact. Below is an example of the column I'm trying to combine. I don't want to do a straight append though because…
-
R Script Sharing
Hello, Appreciate if there are community members to please share a sample of R Script which you are able to run from Dataflow? I have read the documentation from https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/ETL_Actions%3A_Scripting But i'm unclear on the parameters to call a specified column. [Sample]…
-
How to merge two rows in one?
Hi, Let me show you what my table looks like and what I want to achieve this should be the easiest way to explain: Account SubAccount Integration AccountA Subaccount1 IntegrationA AccountA Subaccount2 IntegrationB AccountB Subaccount1 IntegrationA I then want to create an additional column in beast mode that checks if…
-
Leap year in Magic ETL
Hi all, I'm working on an dataflow (Magic ETL) through which I calculate the MTD, QTD & YTD metrics for both This Year and Last Year data so that I compare the values for each one of these metrics. According to Finance, for leap years, when This Year the last day of Feb is the 28th and Last Year was the 29th, this extra…
-
COUNT(DISTINCT creates An issue has occurred during processing. We... Error
I have created this first beastmode calc, and it seems to work just fine. Case when `Total On Hand` - SUM(CASE when `Planned Shipment Date` < (CURDATE()+ 21) then `Outstanding Quantity` else 0 end) < 0 then `Item No` else 0 end When I add a COUNT(DISTINCT to the end, it does not work and gives me the classic "An issue has…
-
how do you format date for comparison to today
I have tried several iterations to compare the date field to CURDATE, CURRENTDATE, NOW and when I attempt to determine if they are equal, I get an error. Does anyone know the proper MYSQL to format for this comparison? case when DATE_FORMAT(`call_date`,'%m-%d-%Y')=CURDATE() then 'Today' when…
-
How to get the second week Tuesday in a month?
How to get the second week Tuesday in a month?
-
Levenshtein distance in MySQL dataflow
Hi, I am looking to implement the levenshtein distance function (https://en.wikipedia.org/wiki/Levenshtein_distance) in a MySQL transform in order to find similar strings. I previously used Soundex to compare strings but it does not perform well on longer strings. How would I go about implementing this function in the…
-
Convert text into a number
Hello, I have a problem with numeric data being recognized in Domo as text (word) data when I'm trying to build my card. I know this is the problem because the data (which is numbers) is placed under CATEGORIES and it is listed with a T symbol. I need help with a Beast Mode formula that will translate this data into…
-
Can i write a nested "if" "and" statement in beast mode?
here is what the excel formula looks like: =IF(AND(K5=0,L5=0),"remaining",IF(AND(K5=0,L5>0),"resolved",IF(AND(K5>0,L5>0),"no gap","new"))) thank you so much for your help
-
How to join datasets without a unique ID
Hey All, Running into a problem, I have a IP address for a user, and wanting to reference the country of origin for IP address. I have imported a IP address database, which shows the IP start and IP end ranges for specific countries. I have modified these and the user IP address to numerical value. The plan was to use…
-
How do I calculate IRR in a beast mode?
-
Beast Mode Numbers Sorting Improperly
Hi all - I create the folling simple beastmode to find the number of days between two dates: DATEDIFF(`Date1`, `Date2`) The formula validated and the output reflected the correct number of days between the two dates - great! The problem is that the card that the beastmode is in does not seem to recognize the value of the…
-
Sum of all instances of dates more than 30 days old
Hello all, I'm attempting to sum all of the instances of dates that are more than 30 days older than the current date to determine which users of my app are no longer active. Below is my beast mode code: SUM(CASE WHEN DATEDIFF( CURDATE(),Max(`Last Sign In`)) > 30 THEN 1 ELSE 0 end) However, ever time I run this command it…
-
Rolling Average Calculation
HI There, I'm trying to do a rolling average calculation. Compare Average of previous 7 days (not including yesterday) versus yesterday. CASE WHEN AVG(CASE WHEN `advertiser` = 'coke' AND (`day` <= CURRENT_DATE()-2 and `day` >= CURRENT_DATE()-8) THEN `revenue` ELSE 0 END) = 0 THEN 0 ELSE (SUM(CASE WHEN `advertiser` = 'coke'…
-
Beastmode calculate percentage of actuals compared to target (NOT total)
Hi, Ultimately I want to show utilization of billable & other hours worked, as compared to their allocated hours. The kicker is I need to account for people working more than their allocated hours, i.e. it needs to show greater than 100%, so I cannot use the built-in Percent of Total graphs. There is a set allocation…
-
Create a new table from existing table with data for last few days
Hi, Let say I have a table that corresponds to a Domo card. This table has a field with type 'Date' Now I want to create another table that is exactly mirror to my 1st original table, but will consider data only for last x-days starting from my System-date. Furthermore, that new table should be updated automatically when…
-
Is possible to send an E-Mail as final step of a DataFlow chain?
Hi, We are developing a dataflow that needs to send an email to a list of users after all the steps are done. I tried to use smtp library in a python script as follows: with smtplib.SMTP_SSL("smtp.gmail.com", port, context=context) as server: server.login(email, password) Giving this error: socket.gaierror: [Errno -2] Name…