-
Convert text to date
Hi All, I am trying to convert a string to date... STR_TO_DATE(`MFGDTE`,'%Y-%m-%d') But I am getting the value as 2019-12-18t00:00-06:00 ...Can you please let me know how to get just the date
-
How to create 2 Output Dataset in DOMO (unable to load this dataset issue)
Hello everyone - Good Day! I just need some inputs. I have a dataflow with 4 input dataset and 1 output dataset. When I add another output dataset and hit preview, it showed all green checks. However when I saved and ran it, that 2nd output dataset shows as "unable to load this dataset"? any reason why it is unable to load?
-
Quick Select Multiple Columns in "Select Column" ?
I am wondering if there is a way to choose multiple columns quickly when using "Select Columns" in an ETL. I do not mean using Add All Columns. We do not know SQL, and we have massive DataSets with a large amount of columns (50+), but only need a mix of about half & half between 2 Datasets. Having to scroll through &…
-
Beast Mode for Sum Function
Hi All, I am using the sum function below which is throwing an error..can you please advise.. CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN SUM(`CURRENT_INVENTORY`,`NONCURRENT_INVENTORY`,`OLD_INVENTORY`) END
-
Using ETL formula for finding between dates
Hi, I have a dataset and every row has a date. I want to add a column using ETL that indicates what week that is (starting mondays). So for a row with 3/28/2022, the new column would have "week of 3/28" and for a row with 4/1/2022 it would also say "week of 3/28" since 3/28 was a Monday. A row with 4/5/2022 would say "week…
-
why is greater than filter not working for date.....????
I am building an ETL and have noticed when I attempt to filter data by dates greater than (or greater than or equal to), Domo will filter everything out. I notice however when I select less than (or less than or equal to), it properly filters out.... What is going on here??? Is this a glitch?
-
coalescing data in a table
I'm attempting to build a table, which pulls from a order history dataset and includes the following key columns: Account Number, Order Src (CSR, EDI, or DCX). When I create beastmodes (e.g. CSR $ = case when `Order Src` like 'CSR' then sum (`order_line_total_price`) end) to break down the orders for each account by their…
-
Beast Mode to remove text after space
Hi All, I am trying to remove the text after space from the below example ..Can you please let me know how to do the same.. R17245 20 should return just R17245 FG789 32 should return just FG789... Can you please let me know how to achieve this in beast mode..
-
Beast Mode to compare the record from the recent Monday to the last Monday
Hi All, I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday…
-
Can I Create Variables in Beast Mode?
I'm trying to create a variable that I can reference a beast mode calculation, but it keeps on telling me "This calculation contained a syntax error". Is it not possible to create variables in Beast Mode? Or am I just messing up the syntax? I've tried: set @month=1 set @month:=1 select @month=1 select @month:=1 But none of…
-
MagicETL/SQL to join datasets using a comparison between values
I am trying to get a geolocation based on the IPNumber. Dataset a: ipaddress ipnumber (translated from ipaddress) Dataset b: lowIPNumber highIPNumber Country Region City I would like to add the Country, Region, and City values to an output dataset: ipaddress ipnumber Country Region City where a.ipnumber >= b.lowIPNumber…
-
IF Condition
Beast mode calculation needed.. If 'Model' value contains blank then ignore those column of data and take of rest of rows model example 1 20 2 --- blank 3 40 here we need to ignore 2nd rows
-
Calculated Fields - Pipedrive ID's
Hi, I am pulling in data from Pipedrive and need to use Calculated Fields to convert Pipedrive Field ID's into text. Is there a way that I can do this once instead of doing calculated fields for each metric?
-
REGEXP_REPLACE in ETL add new line?
I have data in Domo for an address that includes a new line character. This data was brought in from NetSuite in this format. I am trying to add additional data that matches this format. I have the following data: Ship Address: attn~company~addr1~addr2~city, state zip I would like it saved as such: attn company addr1 addr2…
-
Reverting Back to the old dataSet version
Hi team, I changed a few things in my recursive ETL dataset and lost all the rows of data that were recursed in it. When I try to revert the flow to a previous version, it does not show the lost data rows. Any way to retrieve those?
-
How do you rename null values in a date column?
I have blank values in a date column which is messing with my filter, is there a way to rename these? I don't get the option since it's not a text format
-
Manually adding data to a existing DOMO dataset
Hi Team, I am trying to add the data to a existing DOMO dataset..Can you please let me know how I can achieve the same.. Thanks, Arun
-
Add Data that has been modified in the last 7 days
I have a snowflake dataset that has a modified date column. I want to merge the data that has been modified in the last 7 days to the dataset. Sometimes an order is modified after its placed and we want to capture the new values of the order. I have used this query but its not working SELECT * FROM "TABLE" WHERE…
-
Setting a duplicate value in a column to null or zero
I have a dataflow where I'm joining projects to their respective tasks. Each project will have a large number of tasks associated with it and a planned/actual cost for the project. This results in many duplications of the cost and task data. Can I somehow set all duplications, after the first set, to 0 or null without…
-
How to remove empty columns from a dataset?
I am using a DOMO connector to import Salesforce data. The imported data has 30+ columns with no value. Can a query be written to remove all the columns with no values? I know I can write a script using Python/R but we don't have a scripting tile in our package.
-
Line over BAR chart
Hi All, Hope you are doing well!.. I currently have a bar chart to show the this year inventory by Fiscal week... I want to have a line running over my bar chart that shows the inventory for the same time last year...Can you please let me know how to do that.. My beast mode for This year inventory count(distinct case when…
-
Problem with column sorting
Hi All, I am sorting a dataset in the visualizer...I am sorting by the dealer name and whether the item got shipped or not and then by the order date all in the ascending order... The beast mode that I am using for the shipped or not beast mode is the following: CASE WHEN TRIM(`order_status`) = 'order shipped' then '1'…
-
Return data within geographical radius?
I have a table containing a list of zipcodes and corresponding data. Say a salesperson unfamiliar with SQL wanted an elegant user-friendly interface to return all rows within a 5 mile radius of a given zipcode. Is there a way to implement geographical data like this? Or perhaps a free Domo app that can integrate this…
-
How can I prevent a running backlog total beastmode from hitting zero?
I'm working on building a labor model for my department to track where our projected backlog will be over the next several weeks. One problem I'm running into is when the incoming backlog is less that what we're projected to process, the backlog goes into the negative instead of hitting zero which then throws off all…
-
Using UPDATE statement in Redshift
Hi , I am trying to update a column in Redshift using UPDATE statement. But I am getting error If someone can suggest whats wrong with the code I tried this code- UPDATE "table1", SET "created_dt" = '01/07/2022' where "change_hint" = 'update' The output comes as - Whoops ! Something went wrong
-
How to find a max value from multiple aggregates
Hey guys, I'm looking at our collections information from the last two years to find our best collections month during that time frame for each of our locations. Before when I had to do this I just did it in ETL using a group by function, but this case is a little more complicated as I'm wanting a specific set of…
-
showing record with max date
Hello I have a dataset which pulls record numbers(gn_id) and its pulling some duplicates and it looks like if it that gn_id has a duplicate I want to pull the one with the most recent or max date but am unsure on how to accomplish this, I have attached a screenshot of what I'm looking at as on this example would want to…
-
Data Interval Using Business Days In MagicETL
I am calculating deadlines by using a dynamic formula in MagicETL, which adds 4 days to a date like so: CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END ) This works great, however I'm wondering how I could change the interval to simply be four business days instead…
-
Vertical Symbol + Grouped Bar chart Question
Hi All, I am trying to create a vertical symbol +Grouped bar chart to show by Fiscal Month week to show the On order $ for this year and last year and the on order units for this year and last year... Following are the beast modes that I am using X-Axis: Fiscal month week -CONCAT(UPPER(`FISCAL_PERIOD_LABEL_SHORT`),'-WK ',…
-
Beast Mode for Getting the order status of the vehicles on order
I am trying to determine the status of the vehicle on order -Whether it is stock or Sold , whether it is part of the BAM campaign or not and the sold or stock date based on the following set of rules If the data column orderstatus has *SLD ,SLD or SOLD then the comment column is sold ; if the orderstatus column has STOCK…