-
Time to Second Sum Aggregation
I am having an issue with summing 4 different time to second functions. I have 4 different souces of time in the format of HH:MM : SS which were imported in as text and thus converted to seconds and then minutes. I'm unsure why they cannot sum as they should come out as whole numbers of minutes and when used seperately,…
-
Alerts for when Data Sets Fail
Currently if a data flow fails I receive an email alerting me to this occurance. However is there any way to have this type of email alert occur when a data set fails to ingest data from the connector being used? I've had some issues lately that I've luckily caught as we currently only have a few data sets from that…
-
Beast Mode Period-over-Period issues
Okay, so I'm trying to do 4 Beast Modes, and I cannot seem to get the right data. I'm trying to find the average amount of dollars per time period. Here's what I have come up with so far, to no avail: * Current year-to-date* avg(CASE WHEN `Date` >= DATE_FORMAT(CURDATE(), '%Y-01-01') THEN `Dollars column` END) or * AVG(CASE…
-
How to add color to hyperlink in beast mode?
CONCAT('<div style= ""><a href="https:`,' "target="_blank">',`id`, '</a></div>') I'm using this code in beasrt mode. How do I add color to my font?
-
Case When Aggregation compared to another Aggregation
I am looking for a way to get a count of accounts that have had a note closed within 4 days of it being opened. The issue I am having is when an account has a note that is closed within the 4 days but has had another note opened but is not closed within the 4 days within the same time period of the first note. example…
-
I need to export a list of attributes from my data table
Is there a way to create and export a list of attributes from my data table? I don't want the entire table with all of the data, just the attributes. Can I export this?
-
How to do generate_series in a SQL dataflow
Hi, I'm trying to elimate the need for a database between our production system and Domo by doing all ETL work within Domo (I know this may take longer to process). The database is PostgreSQL and some of the queries that I need to do in Domo use a function called generate_series, but it appears there is not a simple MySQL…
-
Recalculate the transformation of the dataflow based on the specific time range selected in the card
Hi everyone! I am trying to create a card to show the number of active customers. Active customers are those whose the sum of revenue and the sum of quantity are more than 0. Practically, it means to exclude those customers whose the sum of revenue and the sum of quantity are less than or equal than zero. The reason behind…
-
Beast Mode division calculation not work
Hello everyone, I've read other related posts and tried with my case but still failed, see if anyone could help. Thanks in advance! I try to get the % of certain rating (with 'green' or 'acceptable'), but I always get the result=2 with my formula. However, it returns the correct count when I do the numerator and…
-
trouble with str_to_date in beastmode
I'm having complications formating an existing date column to my liking with the beastmode function. The existing date column is currently formatted dd mmm yyyy hh:mm:ss AM or PM. This is in a table chart, the chart calendar is using the date column I just referenced, and is graphed by none. Right off the bat, it is weird…
-
Converting text to date in ETL
Hi all, I'm trying to convert a text into a date field in magic ETL, and I'm running into errors and unexpected outputs. Screenshots below. When trying to just convert the (numbers as text) into dates, I'm receiving the error "An invalid date was found". When I conver that column first from text to numbers there's no…
-
Beast Mode to change value in Column A based on Value in Column B
Hi there! I have a table that includes a Site ID column and a Site Name Column. I want to change the Site Name when the Site ID = x. I have the following code which works but it creates a new column but I don't want a new column, instead I want to change the Site Name in the existing site name column. Many thx in advance!…
-
Historical Database I inherited
Hi all. I'm dealing with CRM data, and have a snapshot of all of our data that is appended to a full historical daily database. This is done in a SQL transform, but I am completely unable to figure out why the input data is being appended to the output instead of clearing it. I am completely dumbfounded. I'm attaching…
-
Beast mode as filter
I created a beast mode to calculate $/visit, in my table chart, I wanted to show $/visit when it is great than 0. Any one has an way to work around since beast mode cannot be used as a filter? Thank you. Olivia
-
Counting characters in ETL?
Hi there, I know there's a way in Python to count characters similar to LEN() in Excel, but I don't know Python at all to be able to use it as a tile in the ETL. Can someone help me figure out how to add a new column of character count to a dataset using Python? Thanks!
-
creating a formula for quarters based on value received at month level
for quarter 1 where month = jan , feb , march if data = 1 , then sum = sum+1 else sum = sum+2 basicially i want to write a formula where i need to check that if there is data in months, then add 1 , else add2. and this calculation is based on quarter level. I dont know how to write this in DOMO case
-
Issue changing string date to date in MYSQL ETL
I'm working on a query where I'm fed in a month name and need to change that into a date. I've formatted the string so that it looks like '2019-11-01' and such, but when I try CAST, STR_TO_DATE, DATE_FORMAT or anything else, I get a blank into the field value. I'm not seeing any errors. Any thoughts on what I could be…
-
Average Data before Combining in ETL
I'm trying to create an end result of comparing Rain to Sales by Day. I pulled Rain data from the NOAA connector for the entire city, which ends up breaking it out into individual Stations across the city. The issue I'm running into is that when I combine this with Sales data for the city, Domo adds the total city Sales to…
-
Split dates in single column into multiple columns
I want to take a column that contains dates and split it into at least 2 columns to find the date difference between the two. So, for example, I have a client ID and a transaction ID that are unique. The transaction ID does not repeat, but the client ID does based on their purchases. My date column is their purchase date.…
-
Adding Days to Date
I am trying to add 7 Days to my date in SQL Transform. When I simply do Date + 7, the date is larger than the end of the month it shows as a larger number(2019/08/30 becomes 20190837). How do I add days to date. Select date + 7 from table
-
Sorting question, why are values of zero counted highest?
Hi, I am sorting a table of highest to lowest based on yearly sales. An issue I have is that sales reps with 0 sales are always sorted to the highest position. This is annoying because I am unable to place them at the bottom, even by creating beast modes. I am not sure how to exclude them from the data set I would either…
-
Magic ETL Exclusion
I'm looking for an efficient way to only keep contacts that appeared in the year 2018 and exclude contacts that appear in the year 2019 AND 2018. It's possible for a contact to appear several times in the same year, so I cannot simply use the Remove Duplicates tile. In Magic ETL, how would I do the equivalent of: SELECT…
-
combine multiple fields from one table into one main field
hi everyone! I'm working with a dataset in which every new transaction creates a new 'transaction date' field as oppose to keeping the dates within one field. see sample attached. I'm trying to figure out how to combined the fields 'transaction 1 date' thru whatever the number of transactions may be, into just one…
-
Multiple Level Aggregation
Hi all, Running into an issue when trying to do multiple levels of aggregation in a beastmode calculation (not sure if it is actually possible as a beastmode). I have a dataset that looks something like the below with data at the item-location-day level and a servicing DC associated with each store. What I am attempting to…
-
Importing a value with leading zero
Friends, I am importing a simple dataset, and the leading zeros are being truncated. Example: I want: 000001 But end result is: 1 Does anyone have any suggestions about how to work around this? Thanks.
-
Table Cumulative Sum
HI, I am trying to get my data in the left to look like my data on the right. I've seen some similiar cases but the once I see are moving into a line graph and I need these to be in table format. Current WantFIscal Month and NameLY $TY $Plan FIscal Month and NameLY $TY…
-
Rank & Window Function
I wanted to create two static fields for two different tabels coming from WB. 1.One is ID- unique # for each row. 2.Source- which differentiate which data belongs to table A and Table B eg : Union ALL both tabel A & B final o/p shall be : ID| Product| Class| Source 1 |AAA | C | table A 2 |ABC | D | table A 3 |AAA | C |…
-
Using Explain Plan to check Mysql query performance
Hi, I am trying to run Explain plan on my queries to check query performance. When I use the SQL transform I get a message saying the statement is valid but it does not show any output. When I use the Table transform it throws an error saying it needs to be a SELECT statement. So I was wondering is there any alternate way…
-
How to add the unique numbers to every row of the output?
I have dataflow which consists of ETL o/p( table 1+Table 2) as i/p in sql. and further this I need to send it to another department, so they can fill in the blank fields. Thy will be able to fill in the blank fields only if I add the unique Key to each row of the o/p 1.I did add the Index of General type- looks like it did…
-
How to export the data from SQL data flow ?
I have created dataflow, and want to send it to another department to put their inputs, so how can I export the data from SQL dataflow ?