-
Case when SYNTAX
Hi all, I am trying to write a case statement for a column when we see a null to grab another column. I am not sure why I am not getting the output expected. Can someone take a look and maybe advise? Essentially 36534370 and 36534369 from the investor_loan_id column should be in the left column as well. **I've tried to…
-
How do I fill text/values down in the same column for different partitions
I am trying to fill the text/values in "Drillable P&L Group Names" and "Sort Order" down in the same columns and can't figure out how. Keep in mind that the attached dataset is a single partition of a much larger dataset that contains multiple Client Names, GL Accounts, etc. And I'm trying to find a solution that would…
-
Looking on a specific date
Hi all, I am working in Magic ETL. I have group two different datasets on their 'lock_dates' I have created a calculated column where the number of locks are counted in each data set. Then I created a column that told me if dataset 1 matched dataset 2. All of this is shown below The issue I am running into is seeing that…
-
Is there an alternative for the add constants tile?
Hi Everyone, I'm trying to establish a new column with the same value for every row. But instead of a manual floating decimal input through an add constants tile, I'd like to generate the value from the magic etl auto calculation every time the dataflow runs. I know there is a simple way to do this, but the solution…
-
I'm facing 'An IO error occurred' in the input dataset step, what does that error means?
-
How do I create a matrix from two unique lists in magic ETL?
How do I create a matrix as shown in the "Result" image based on the two input lists provided (zeros filled in the field)? I am trying to do this in Magic ETL. Thanks in advance! Gregory
-
How to Unpivot or Explode Variable-Length Lists in Domo to Flatten a Table?
Hi Domo community, I have a dataset in Domo where the number of postcodes in a cell can vary. Here's an example of my input data: Network State Postcode A VIC 3000, 3001, 3002 B NSW 3011, 3021, 3001, 3004, 3012 I need to transform it into the following format: Network State Postcode A VIC 3000 A VIC 3001 A VIC 3002 B NSW…
-
Comparison of Tasks Submitted vs Tasks Completed
I have a table which tracks tasks from submission to completion. I have made a card to show tasks submitted per month and one for tasks completed per month. I would like to have a single card with grouped bars to show these compared against each other however the bars always seem to end up the same size as if it's only…
-
LEAD IGNORE NULL in Magic ETL?
Hi there, I am trying to rewrite a Redshift Dataflow into Magic ETL. I ran across a specific function that was available in Redshift that I dont think is possible in Magic ETL. I wanted to get the community's input and advice to see what you guys think. This is the function found in Redshift: LEAD(CASE WHEN t2."channel"…
-
How to use value from beast mode as a value for all?
Hi, I am trying to figure out how to best solve my issue. I currently have a calculation in my dataflow attached to a certain account code for our organization. That value changes daily but I need to use that value to divide the rest of our account codes by so I can't just add a constant value. Because it is attached to…
-
Employee count by month
I've been asked to build out a card that shows employee count by month, this seems easy enough until you try and factor in the termination date. What is expected is to show in each month the total active employees, so if employee A is hired on Jan 1st and terminated Feb 15th he would show as a count of 1 in January and a…
-
Using a Dashboard Filter to generate Purchases From 12 Months Prior to start date
I have a dataset that shows details from received Purchase Orders (inventory) that our company buys. If for instance a user sets up a date filter on this data, we also want to show the received purchase orders for the same vendor for the receipts starting 365 days prior to the Start date of the Date filter on the…
-
Failure to parse date/time
Any help on this would be appreciated, I can't seem to get rid of this error and I'm not sure what I'm doing wrong or what I need to check. I'm duplicating an ETL we currently use for a similar dataset, but this dataset is separated by day, rather than month.
-
Is possible to made a "not in" condition in PDPs?
Usually I use PDP giving access to some areas, but for exceptions I can not just give access to everything, like Employees of Area A but not access to a specific employee out of 300 is posible to use a not in for a PDP?
-
Formatting Summary Number For A Calculation
Hello All, I'm struggling here as I feel like I'm so close. I'm trying to show a summary number that is a calculation (Cost/Leads) and while I've been able to figure out the font size and color, I am still struggling to control the format to be a currency without decimals instead of the number its at now Right now, I've…
-
dynamic date associating
I have a dataset that contains IDs and meta data to include modification dates. Each ID has a last modification date. However, each ID may (or may not) be associated with other IDs. I want to identify the latest modification date (direct or associated) for each ID. Below is an illustration of what it looks like, except…
-
Date Format change
I have a few columns which use the date format 01/Jan/2016, I need to convert these to 'Date' to use as date ranges in metrics. I have tried using the 'replace' text card in an ETL to change the forward slashes and months to numbers. This works on some rows but not all (trying to figure out why). Then I change the data…
-
Comparing Datasets to Determine Variance
Hello everyone, I am working on comparing data from a previous data input, Universal Analytics, to a new one, Google Analytics 4. Basically, there was an update and data is formatted differently. I am attempting to join the two datasets to compare the month of A ugust for variances, but the closest thing to a unique column…
-
How do I remove the 'Total' row from a data import?
I am using a connector to pull data in from one of our web analytics platforms. The problem is that it pulls in a 'Total' row which I can not suppress at the source. Using an ETL how do I remove that row? I've tried to use the Filter Row tile but perhaps I am doing something wrong there because I have not been able to…
-
Can you use Magic ETL to 'explode' table of transactions to all days?
Table A has transactions that show an item's "Old Status" and "New Status" along with the date of the transaction. For example, row 1 shows that item XYZ changed from "Regular Price" to "Promo Price" on 1/1/2023. Row 2 shows that item XYZ changed from "Promo Price" to "Regular Price" on 1/4/2023. Row 3 shows that item XYZ…
-
MySQL Rank over Partition/Window Functions
Hello! I know there was a post in September 2018 talking about how Domo's MySQL version doesn't support Window functions thus you can't do Rank over partitions in MySQL dataflows - anyone if this is planning to change? It's cumbersome to have to kick out datasets to Magic just so I can rank them using partitions. Thanks!
-
Unpivot and Pivot
Happy Friday! I'm not sure how to start. The data in the yellow section is what I currently have in the magic ETL. I want the result to look like the data in the green section. I've tried to Unpivot the data and Pivot them but have been unsuccessful. Could someone shed some light on this issue? Thank you very much!
-
How to get an avg in the grand total of a column with a BM with sum?
Hi, Im trying to use this BM CASE WHEN MONTH(Cycle)='10' THEN (sum($ New Annual Salary(in USD))/ sum($ Annual Salary(in USD)))-1 ELSE sum($ New Annual Salary(custom)(in USD))/ sum($ Annual Salary(in USD))-1 end and its sucessful but in the Grand Total I requiered an average not a sum, and usually I can use the agregation…
-
Calculating Week on Week customer retention using beast mode
Hi, i need assistance calculating week on week customer retention using a domo beastmode on a transaction dataset. Dataset appends new transaction based on the transaction date so a single customer has multiple rows of transaction distinguished by transaction data
-
Remove duplicate sites that have different 'operators'
Hello, I have a large list of sites where there are multiple duplicates of the same 'Site Address'. I can't just use the 'Remove Duplicates' tile in ETL as there can be multiple panels on one site. I'm looking to sort the sites using the 'Latest update' date field. Then I'd like to remove sites that have the same address…
-
Add data to an existing Excel file in Domo?
Is there a way to add monthly data to an already uploaded xls file in Domo? Rather than having to upload a fresh file each month, I was hoping to be able to easily just upload an initial file at the start of the year and then add new rows of data (sometimes up to 100,000) each month. This would also save me having to…
-
How to use Rank function to fill in the data
There are two tables, the Existing Pay/Bill Table and the EditHistoryPlacement Table, that I wish to join together using the PlacementID column in order to generate the data in the EXPECT Existing Pay/Bill Table. I'm considering the use of the RANK function (with dateAdded… as a parameter) and the PARTITION clause (with…
-
Find Most Recent Date
Hey all, I am trying to find a way to locate the most recent date in a table based on rows of multiple columns. In other words, given a single case with variables X1, X2 and X3 I want the beastmode to find the latest date. So if column 1 was 1/1/2015, column 2 was 2/2/2014 and column 3 was 5/1/2016 then the calculation…
-
Using RegEx to split a column at a delimeter
I currently am working with a column that is delimited using underscores. I was able to write the following RegEx in a builder which was able to do what I needed: ^(?:[^_]+) What is the best way to implement this RegEx to return the part of the string that this captures?
-
Criteria Based Triggers
Greetings, It would be great if we could schedule ETL to run on multiple yet very specific criteria. The one I have in mind as an example would be if the data set is not triggered by a prior dataset updating to allow for the system to run it at a certain time. I know we can set it to do this now but in that case it would…