-
Data Extraction from Teradata
Hi All, I need to create a DoMo Dataset by extracting a data from Teradata. Can anybody help me know which connector needs to be used and steps to fetch data from Teradata? All your help is appreciated.!!
-
Monitor last run of datasets
Hello Community, We are looking for a way to monitor the last run of our email datasets. At the moment we are feeding the data through Email either manually send to Domo or via Obiee, however from time to time either someone forgets to upload the data or the automatic upload fails. As a solution, we would like to build a…
-
CAlculated Average to include in my datasource
Hi All, I need help since I am not familiar with sql, but I need to a column with the calculated average for a particular product which does not have averages available in my datasource. This is a small sample of my data: As you can see the AUM product does not have an average balance in the source system hence it has to…
-
Empty cells after aggregating and creating a case when variable
Hi I have a dataset that shows a sale/lost sale an agent made on a daily basis. Now I am using the dataflow to get the sales % over the last 3 months (data is only last 3 months) per consultant and want to join it back to the original dataset for later card creation purposes. This following formula gives me empty values…
-
Summarize many columns in MySQL
SQL newbie. We have a dataset which is sourced by a google-doc. The data has many columns with short strings in them. Our goal is to summarize it to two columns Column 1: Order Number Column 2: A concat of each column on a given row (with the column headers prepended in) I have created a transform that extracts all of the…
-
MySQL joins are not case-sensitive!
I just realized that joins in SQL in DOMO are not case sensitive! This is a huge problem for me and my team. Here's an example: table_1: teammanager_idSalesJohn123MarketingJoe341 table_2: locationmanager_id_2MichiganJohn123CaliforniaJOhn123TexasJOHN123 selecttable_1.*,table_2.*from table_1left join table_2 on…
-
Unsupported SQL type '{columnType}' for column 'VARBINARY'
I want to assign NULL values to certain columns in a SQL transform: SELECTNULL as 'column_x',NULL as 'column_y',column_zFROM Table_1 The dataflow gives the following error: Unsupported SQL type '{columnType}' for column 'VARBINARY' This is because from what I understood from another post in Dojo: you cannot assign NULL…
-
Tenure calculation using differences between 2 dates (magic etl)
Hi, I am creating a new variable using the add formula icon where I am creating time period bins that reflects the tenure of the consultant in reference to today. This is the formula I created: case when CURRENT_DATE() -`CD (Start)` < 90 then '3 Months' when CURRENT_DATE() -`CD (Start)` >= 90 and CURRENT_DATE() -`CD…
-
Create Task on Dataflow Output by Row
Hi! If this isn't the right forum to post this, please let me know. TL;DR: I'm trying to automate alerts/tasks from the output of a dataflow such that each row in the output gets its own task instead of the entire updated dataset getting one bulk "dataset is updated, here you go" task. Two failed attempts are detailed…
-
Does datafusion count against warehouse row count
Reading the documentation for datafusions: DataFusion: Advantages and Disadvantages * Because a DataFusion is a just a view of the data, the output doesn’t have to be materialized and indexed into the Domo databases, which makes it so that data can be combined, visualized, and updated very quickly in Domo. * It makes data…
-
Beast Mode validated but not working
My Beast Mode shows it is validated, but then the numbers will not display on my graph. The error message I get says "An issues has occured during processing. We are unable to complete the request at this time." How do I fix this beast mode to get it to work properly? SUM(`Item No`) / Count (Distinct `Customer ID`) or I…
-
Filter Field with LIKE based on another Field
Hi all! Hopefully someone is able to understand what I'm trying to achieve and help with a solution. I need to filter based on a "Tag" field with single text values, once selected would filter all relevant items in another dataset that has these tags in a single field "Tags" with multiple text comma delimited "tag1, tag2"…
-
Multiple Case Statements
Hi Everyone, I am having a bit of trouble stringing together multiple case statements... The end goal of my attempted beast mode is to calculate Average Weekly Cost Per Lead (CPL is Cost divided by # Leads). The wrench in this whole scenario is that I have to do this based off the broadcast calendar. Broadcast Calendar is…
-
How do you change the order date "2020-01-06T16:56:00" to the first day of the month?
How do you change the order date "2020-01-06T16:56:00" to the first day of the month? Via transform.
-
Grouping into one Row
I want to be able to group the parent_wID field so that it is one row per entry. As you can see, the Retail/Wholesale columns are build from beast modes from a separate field. This is the syntax i used to separate those. "Retail" (CASE when `Loan_Originator_Type` = 'Retail' THEN 'Yes' else 0 end) "Wholesale" (CASE when…
-
Trying to filter records, but something is going wrong
Hi there, I have a single dataset. I am trying to filter records based on 3 criteria. For example, I would like the row to be included in the resulting data is column A = 7, or column B = 7, or column C is not null. I have tried a single filter, including the three above conditions and set the rules to "any" (not "all").…
-
Issue with Nested Case Statements
I have a dataset of call records. I am attempting to filter out records that fall outside of business hours when the 'Call Result' is 'No Agents in Queue,' based on the product the call was associated to. I have created the following Beast Mode calculation to do so: CASE `Last Leg Product`WHEN 'NPAC-CCS' THEN (CASE WHEN…
-
Whoops! Something went wrong.
I have a dataflow with 50+ transforms that has been working. Recently I added some more transforms to it and now when I run the final transform that joins all the other transforms together I get this error in red "Invalid" next to the RUN SQL button. Usualy if there is a syntax error or a missing transform it will tell you…
-
how do I convert data time field to yyyy-mm-ddThh:mm:ss
so that when i export the file shows 2020-09-14T11:56:43 instead of 14-Sep-20 11:56:43 ?
-
Year over Year totals by month and sort
I am trying to create a bar chart that contains totals for 2 types of requests and their lines of business. This card will be a monthly card and would show a comparison of current year versus last year for that particular month. Example: y axis LOB A + Request Type A total for 2020 LOB A + Request Type A total for 2019…
-
How do I find records created within the last one hour
I have a data set with a Created Date field and I want to get data created in the last hour. What filter should I use? I think using a filter of `CREATED_ON`>SUBTIME(CURDATETIME(),lastonehour) will work but not sure the value to specify for lastonehour...is it 3600, 3600000, etc? TIA for your help. Yogesh
-
[Beastmode] Like binary - to find uppercase text
When I use the following (example) query it's working fine: (case when `Product` like '%PROduct1%' then 'Productone' else 'Ignore' end) Except that product1 also turns up in my results and I only want PROduct1 which uses uppercase letters. In mysql you can use "like binary" but when I change it to: (case when `Product`…
-
Case when.. multiple parameters
I would like to make a beastmode for a column that contains integers, as well as null values instead of zero. However, my current formula returns null as zero, but the metric that i use is for purchase price variance, and one of my accounts has duplicate values for invoice price and ppv. For example, it would show $65.00…
-
How to find first non-zero number in TEXT data type in DoMo ETL (Not Beast Mode)
Hi, I have a business requirement where i need to find first non-zero number in a TEXT column which has leading zeros. The position of non-zero number is not fixed in text column. Can Anyone help me to find logic for the same in Domo ETL? Below is the example where i need to find whether the text begins with number 2 by…
-
Why is the Group By tile in an ETL giving me an error message?
I am trying to collapse my data set into a single row for each date, publication and product and get the error message "Action outputs to an invalid number (0) of actions" My data is structured as per below:- Date Product Publication Channel Revenue 1/1 Display Publication A OMP £1000 Where Date, product, publication and…
-
Percentage Summary Number using Beast Mode
I have a graph that displays my company's net income vs. our budget. In order to calculate the net income, I use the following beast mode: sum(case when `Group 2 Name` = 'Sales' or `Group 2 Name` = 'Sales Returns' then `Amount` when `Group 2 Name` = 'Direct Costs' or `Group 2 Name` = 'Protein Powder' then -1 *…
-
Over Partition Clause in Magic ETL, or SQL transform
I'm looking for a solution to create a new column that will either copy a shipping timestamp on all rows, if the shipping date exist on one of the rows. If the shipping date doesn't appear on a row, then it can be null. Or maybe more preferable, a true/false statement, if a shipping timestamp is associated to an account,…
-
Magic ETL trigger timing - input datasets of varied size
I think I just discovered that if a magic ETL is configured to run "only when datasets are updated", and has multiple input datasets, it will start running as soon as the smallest/quicket input dataset has been updated. If a larger/slower input dataset finishes updating before the ETL finishes running, the ETL will not run…
-
Keep first occurrence of value and set subsequent values to 0
I'm working with invoice data that has multiple line items for each invoice. The issue is that the tax field has the entire invoice tax value on each line item as shown below: | Invoice | Item | Amount | Tax | |---------|------|--------|-----| | 1 | A | 10 | 6 | | 1 | B | 20 | 6 | | 1 | C | 30 | 6 | What I would want |…
-
Is it posssibe to create a static dataset from 7 input datasets.
Is it posssibe to create a static dataset from 7 input datasets. because i want to delete these 7 input datasets