-
Setting up a count off of different date rules in ETL
Hi everyone, I'm trying to set up a column that would show me how many workdays to pay a consultant for, depending on how many days they worked for over the month. The variables are whether the start or end date was during that month (and so they would only be comped for that part of the month - from start to month end of…
-
Magic ETL formula Help
Hi All, I am trying to use the following transformation for joining but I am getting an error...Can you please help! CAST(`DEALER_NO` as varchar(8)).. I am getting the following error... Syntax error in expression at character 20 on line 1 : cast (dealer_no) as varchar()) Thanks, Arun
-
How to always correct the date to the 1st.
Hello all, I want to know the code for ETL "Add Formmura" function. The column ”Data” will always contain a value for the year, month, and date. When the value comes in , I want to set the date value always to 1st by using " Add Formmura" function in ETL as like below picture. Could you let me know the code to enter into…
-
Beast mode help - a count of users who transact once a month in each of the last three months
Hi, Any Beast Mode gurus able to help me with how they think it would be possible to return a distinct count of users (customerid) who have transacted (transactiontime) at least once a month in each of the last three months? The table has every transaction made as a single row with a time and date stamp if that helps.
-
How do I get non-matching records from two tables in Magic ETL
I have two tables A and B , a unique record is identified by an ID and a date. I am interested in identifying the non-matching records in the two tables and using the output to build a report,how would I go about this?
-
Assigning the same value to different days of the week
Hi All, I am using the following Beast mode (Goal) to assign the same value to Monday ,tuesday, wednesday and thursday for different plants..Can you please let me know if the below is fine.. Case when `Plant` ='Celes' and dayofweek(`orderdate`) in (2,3,4,5) then '50' when `Plant` ='menes' and dayofweek(`orderdate`) in…
-
Extracting the Date Based on a pattern
Hi All, I am trying to extract the date from one column based on a pattern..For example in the table below if the column orderstatus has a * in the beginning and then there is a date of the format mm/dd/yyyy then I need to extract that date as the soldorstockdate otherwise I need to extract the date from orddate as the…
-
Checking if first character is a *-Beast mode
Hi All, I am trying to flag the rows that have the first character starting with a *...For ex: *SLD 05/11/2022 - I need to flag this 1 ..*SOLD 05/15/2022 -I need to flag this 1 ,1277489 STK#39298.32831 * -I need to flag this 0..can you please let me know how to do this.. Thanks, Arun
-
Looking for Pattern in Data
Hi All, One of the fields (orderstatus) in my report has values like 37141 SLD BAM , SOLD BRANDON BOX 36790...I need to look for values SLD and SOLD ..If these are present then I need to flag that row as Sold otherwise it is stock...Can you please let me know how to achieve this using Beast mode
-
Can you filter data from input dataset in a MySQL Flow
Hello, I have a dataset that has data for the past 20 fiscal years. I use it in a flow that only looks at the last two fiscal years and when the flow runs it takes a good 10-20 minutes just to read the data in the dataset and then start transforming it. I was thinking of expediting the loading process by pre-filtering the…
-
Writing Beast mode with Data label of Measures
Hi All, I have three measures - Open qty, onhandunits and openpoqty... When they are brought into the table in visualizer they are brought in as sum(Open qty) , sum(onhandunits ) and sum(openpoqty) and then given different data label names Now I am trying to create a filter using the condition case when sum(open qty)>…
-
Group By -- all additional columns
I have a data set that looks something like this: Rep Name --- Department --- Month 1 --- Month 2 --- Month 3 etc. I want to do a 'group by' as some reps appear on multiple rows and I want to sum the Month total for those reps. That part is easy. Is there a way to automate that all future months added also get that…
-
Can someone help me calculate employee turnover in Domo?
I'm relatively new to Domo and wanted to reach out to you about calculating Employee Turnover rate. I saw a previous thread (https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Turnover-Calculation-Beast-Mode-Help/td-p/31638) but wanted to see if someone could give me some tips on this specifically. I'm looking to get our…
-
Combining Row Data
Hi everyone! I've asked this question before and was advised to use a pivot table to combine two rows into one, but that would create new columns which I don't want so I'm going to see if anyone else has a solution. I have a dataset that I built that tracks activity by students at a University and I need to combine two…
-
Information missing after Join Data
I have a report just under 3,000 rows. (Left Table) I have a master list with just over 20,000 rows. (Right Table) I'm applying a filter to narrow down the master list and then using Left Outer Join Data to pull matching criteria using an email address as my join key. Unfortunately I'm not getting all data from the master…
-
PDP Settings - Is there a limit to the number of columns associated with one policy?
I'm working with a weird dataset/conditions, and wanted to associate 18 different columns and their values to one pdp policy. I made these policies using the standalone GUI utility https://domohelp.domo.com/hc/en-us/articles/360043439353-PDP-Policy-Autocreation. However, I'm getting weird errors. The PDP policy just isn't…
-
OR statements in card filtering
Hi folks - We have a dataset where users can have datapointA and datapointB. We want to build a card where an end user can have a filter that asks for any user has datapointA = 'green' OR 'databpointB = 'circle'. When we put those two datasets in a dataflow/card and add the datapoints as Quick Filters, it automatically…
-
Date_Format Fiscal week number and Year
Hi All, I am using the Graph by capability in DOMO Visualize and I want to get a filter to display WEEK 24 -2020 , WEEK 25- 2020 ......WEEK 17 -2022 in descending order I tried this DATE_FORMAT(`SHIP_DATE`,'%Y-%w') ....But its not correct..Can you please let me know how to go about this.. Thanks, ozarkram
-
Performing Calculations on Timestamps
Scenario: I need to calculate how much working hours time has passed between a creation date and a completion date. Parameter Exception: Item can be submitted after hours, but obviously that time wouldn't be counted until 7am the next day. Example timestamp: 2019-12-10T16:24:48Z Working hours: 7am to 8pm Working days: M-F…
-
Max number of columns in a dataset?
Hi, What is the maximum count of columns for a dataset? Thanks!
-
How to build a sales by state US Heat Map DOMO CARD Drillable into different vendors (US MAP)
Hi everyone, Just wanted to check if there is an article that will help build a sales by state heatmap for US States (on US MAP) drillable into vendors... Thanks, Arun
-
Beast Mode to remove values based on card selection?
Good afternoon, Is there a Beast Mode that can be written to exclude certain values from the broader data set based upon the option an end user selects on a page? Example: users can either choose AM or PM on my dashboard., which then alters the data on the rest of the page based on which they selected (the default is no…
-
Beast mode to show only one month
Have a dataset for fire drills we have several locations that run them every month i want to create a beast mode to only show me one month so i can duplicate it for each month to show one column per month to see who didn't run their drills month to month this is what i have but it is not working i will show you an idea of…
-
How do Dataflows impact Dataset Certification?
I'm exploring the option of implementing Card/Dataset Certification for our company. I need a better understanding of how a DF can impact the certification of a Dataset (DS). My basic understanding of DS Certification is that the certification will expire on the DS if there is a schema change (i.e. Columns deleted,…
-
Beast mode for count distinct by concatenating two columns
Hi All, I am trying to do the following beast mode operation: COUNT(DISTINCT (CASE when `ord_date` < CURDATE()-1 then CONCAT(`sales_no`,`part_no`) end )) The expectation is the distinct combination of sales_no and part_no ..But I am not getting that..Can you please help me here.. Thanks, Arun
-
Day of week Beast Mode Code
Hi All, I am trying to do the following calculation in Beast mode but I am getting some errors.. Can you please help me with this.. Case when Plant ='Celes' and dayofweek(orderdate) in (2,3,4,5) then '50' when Plant ='menes' and dayofweek(orderdate) in (2,3,4,5) then '90' end Would appreciate your input! Thanks, Arun
-
Syntax of ANOVAF(), ANOVAP() and TTEST()
These functions are supported in the Fx Tile in Magic. Does anyone have some syntax examples ? The function requires an aggregation context suggesting a group by action
-
Generate Date Range Column from Two Date Columns
Hi all ! Im trying to generate a date column with a range of dates from two columns: Table I have (date format (mm/dd/yyyy) ) Id start_date end_date field1 1 01/01/2019 07/01/2019 text1 1 07/01/2019 12/01/2019 text2 2 01/01/2019 02/01/2019 text3 The idea is to generate a list of dates between start and end date with an…
-
String to Date
Hi I'm trying to convert a string to a date where the string shows "day Month", ie "30 Aug" or "23 Jul" (double quotes not included in the string). The string to date statement doesn't seem to work. Any ideas? Thx John
-
Domo Python error
i am using the python tile in domo magic etl i am trying to cast a column to a category but when i try and read the dataframe to the write_dataframe i get an error # Import the domomagic package into the script from domomagic import * # read data from inputs into a data frame raw = read_dataframe('Source 2') #print('Before…