-
How to solve this problem in Magic ETL - Link items to Header rows
Hi All, Currently we have a dataset which have records in this format: First row is the header record and next three rows are items of the this. but there is no link. I have ranked the dataset and the used a Lag function to join the header and item. Then I have performed the self-join to get Header information to First…
-
How could I generate an end date for each row of data based on the start date of another row?
I have a dataset that gives all records for all evaluations submitted. I need to generate an additional column in the dataset that records the summary_end_date. The start date of an evaluation is the value in the row for the summary_date column. The summary_end_date needs to be the summary_date of the next iterative…
-
How do I change the field type for a column on an existing table?
My company recently released a new version of our online platform and I am mapping its new database fields to my already existing Domo reporting tables. In the previous system we used a simple integer as the primary key for all of our tables. In the new version we have switched to UUID values for many of our tables.…
-
Data Flow Help
Ok this one is going to be a long one. My company keeps track of loans on an excel sheet. We are trying to migrate that excel sheet into a dataflow onto domo. There are a lot of different data sources we pull from to create this spread sheet but I am trying to bring together and formulate an ETL from just two sources right…
-
Magic ETL or Beast Mode?
Hey everyone, I'm using data that basically needs an (=if) statement like we were going to be using excel-- We have two columns called current income and qualified income. The 'income' column needs to populate with 'current income' if another column, called 'instrument' reads 'first home' otherwise, we need to use…
-
SQL Flow taking hours to run
I am trying to run the following flow using the following SQL code: select adv.`Account`, adv.`LDGRDATE` Adv_Ledgerdate, cre.`LDGRDATE` Prorate_LedgerDate, adv.`Total_Amnt`, cre.`Total_Amnt` Prorate_Amount, adv.`Ledger Type`, cre.`Ledger Type` Prorate from `test_grp` adv left outer join `credit_prorate` cre ON…
-
Convert null numeric values to '0' in Magic ETL
Trying to convert null values in an numeric column in an Excel spreadsheet in order to perform calculations on that column. Currently unable to find a work around
-
Why are my filters not working in Magic ETL?
Hi all, I'm confused as to why my magic ELT filters are not working properly. Is it something I am doing wrong? How do I go about fixing this? In the first picture, I am in the data set and have set a filter on the 'time_stamp' column and its filtering the data just fine. For whatever reason, when I go to magic ETL and do…
-
MySQL Indexing Issues
When setting up Indexes in my SQL dataflow, I notice sometimes that a few columns in the join are not made available for indexing. Why is that? What can I do to fix this? I suspect without the indexing, my dataflow is taking an inordinate amount of time. Also, in the Domo help on optimizing data flows with indexing, the…
-
Period over Period help
@GrantSmith Hi Grant, I have used your date dimension solution for lots of PoP charts but like you have mentioned on your post doing month over month can be tricky. Thanks for your help so far on my domo journey. My problem. I have daily data and have a period type of 'Last Month' and 'This Month' I have the data in a…
-
Google Analytics - Referrals
Hello, I am trying to create a graph using Google Analytics, specifically on referrals (Acquisition -> All Traffic -> Referrals). However, when I connect and select which data to include, I am not pulling the right numbers. Does anyone have advice on which metrics to pull in? Thanks!
-
"current year, current month" and "previous year, current month" Beast Modes
Hi, I'm trying to create a 'current year, current month' and 'previous year, current month' beast mode. To this point I have created a current year to date but am not sure of the syntax. I have watch multiple videos and have searched high and low through domos resources. Any help with the syntax would be helpful Here is…
-
Fiscal Year Comparison
Hi, I am trying to create a running total of fiscal year 2023 vs the same time in FY 2022 and FY 2021 but all on the same chart. Right now, I currently have this Essentially what I am trying to do is take the three green bars from July 1 of this year, combine them into one column and then do the same for the pink and blue.…
-
Correcting date format in Magic ETL
Hi all, First time posting here. I need to know how to correct date formatting within a dataset. The dataset was pulled from Google sheets. As you can see, in ETL the formatting is wrong and inconsistent. How can I correct the format in ETL and make everything streamlined and compatible in the analyzer? Thanks
-
Magic ETL Help
Hi, I am going through the Data specialist practical test and am running into troubles. Here are my instructions: Up to this point, this is what I have done: Because I am not an admin on my company network, for those familiar to this test, I was unable to complete the join on the workbench due to accessibility issues.…
-
Multiple Columns with Multiple Yes fields
I have a datasource that looks like this (with ~50 different products, so having just four separate filters would not work): What I need is a table, filterable by product (using OR functionality, if C & A are chosen Customers 1, 3, and 4 should show), with the number of software seats for that customer, and a grand total…
-
Year over year cumulative average
I am trying to create a line chart with the year as a series. The data points are per month with a cumulative average. Data: Year Month Closed Cases Total Cases % Cumulative % (Cum. Math) 2022 1 50 100 50% 50% (50/100) 2022 2 100 150 75% 60% (150/250) 2023 3 100 100 100% 71.43% (250/350) etc. across multiple years where…
-
Formula for rounding time to nearest minute
Hi, I need to round times to the nearest minute. Not rounding up or down, but nearest minute. Any ideas on how to use formula ETL tile for this? Thanks!
-
Population calculations in beast mode?
So, I've got a dashboard in which I'm trying to show the population over time at a facility for one of my cards. So, x-axis should be a date, and y-axis should be how many people were present at the facility that date. The dataset we have has a row for each client, and two of the columns are date admitted and date…
-
What is the best way to calculate?
So I'm using a grouped bar graph and am looking to try and calculate the difference between two of my columns that were created. My company calculates the average spread on loans broken down by lenders we use. I was able to separate those average spreads by product by adding the products to the 'series' drop-in at the top.…
-
Find greatest value for each specific ID
Hi, I have a dataset like this: ID Value 1 5 1 6 1 7 2 23 2 24 2 25 I want to write a ETL formula that creates a third column saying the greatest value for each ID is: ID Value Greatest 1 5 7 1 6 7 1 7 7 2 23 25 2 24 25 2 25 25 Thank you
-
Splitting by delimiter vs LOCATE() function equivalent
Hi All, I want to fill the nulls in column A with the values in column B (or C if B is null). A case statement did not work here so I am aiming to achieve this by combining the columns and then separating them by delimiter. Unfortunately, by separating them we are - of course- back to square one. I was able to use this…
-
Beast calculation to work based on Date filter selection change
Hi , Can you please help to resolve issue . I have requirement where current year - previous year variance should be shown based on Global date filter selected from dashboard . Example : when 2022 ( This year selected from Global date filter) sales variance between 2022 and 2021 to be shown . when 2021 ( Previous year…
-
How to create a filter to capture the end of month
Hello Domo community - Good Day! Basically, we want to capture the data from the previous month on the current week. for example, the start of the week is 8/28 and the end of the week is 9/3. we want to capture all data up to 8/31 only. and then data for 9/1 to 9/3 using the start date 8/28 or end date 9/3. Thanks
-
Grand total instead of running total with Count in Rank & Window?
I was trying to get a total number using Count with Rank & Window in Magic ETL but I can only get a running total instead of the grand total repeated across the rows. My output looks like this: jane doe, item a, 1 jane doe, item b, 2 jane doe, item c, 3 But I wanted this: jane doe, item a, 3 jane doe, item b, 3 jane doe,…
-
To Avoid in the title
How to avoid in the title while sending to report (Card) to multiple user via DOMO ' Ignore as follows :- "Including 5 cards Sent by ----------* " detail:
-
Parsing Dates with Different Formats
Does anyone have any guidance for using a formula/function in a beast mode or using the regex functionality to ensure Domo recognizes all of the dates below as the same? 5/12/2022 5/12/22 2022-05-12 5.12.2022 5.12.22
-
how to break a week between months
Hi DOJO - Happy Weekend! Basically, we have a dataset with date columns based on the start of the week and end of the week only. on the example below, I just concatenated the start and end dates so it shows Aug 28 to Sep 03. if its not concatenated, I can put start date and it would show the start of the week which is…
-
Programmatic filtering
We are trying to to embed domo in our web application, Is it possible to use Programmatic filtering without the use of the yarn server and log in users ? We are using SSO so want to just get the domo access token through an API call and create an embed token with filter values will be retrieved from our back end
-
help needed to create weekly cohorts
I'm working with a large dataset that contains time-stamped user sign up data. I'd like to use ETL to define weekly cohorts based on when a user signs up. My goal is to use these cohorts as a foundation for beast mode calculations like "growth in weekly sign up cohorts over time", and when joined to tables containing…