-
How do I return a string column value from the MAX() of another column value?
Let's say I have the following: I want to group by the invoice # by dropping the SKU, returning the Product_Description of the MAX Product_Weight, and the SUM of the Product_Weight. With my given example I should return the following: I have tried in both Beast Mode and an ETL, but my Product_Description is never accurate.…
-
Dividing two integer columns that contain 0 values causing incorrect percentage value display (CTR)
Hi all, I've run into an "issue" where I can't seem to get Average/AVG values to calculate correctly when using a custom beast mode. My issue sounds similar to this issue here, where some rows contain a "0" or Null value that messes with the aggregation. When I try to replicate that solution I get another confusing /…
-
Calculating the count of entries not present in the future date in comparison to the previous date
Hi All, Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the count of the number of entries that have gone…
-
Filling in blanks, based on next value
I have a dataset in which a User ID will be identified during a session.... and then later change User IDs... and in between, there are rows of data in which the User ID is not identified - but I want to fill in those gaps with the next User ID that is identified during that session... Row -- Session ID -- User ID --…
-
ETL or beast mode for Dynamic assignment of month based on current date
Hi All, Hope you are doing well..I am trying to convert a plan table as below into the table below based on the current date and the reference table Reference table: So I need to basically map the column names (FC1,FC2,FC3...input table) to fiscal month based on the current date and looking up the reference table for the…
-
Looking up values from secondary data source but for multiple columns in primary data source
So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL: -I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource"…
-
How can i write this sql statement in domo?
where a.Customer NOT IN(select distinct Customer from CustomerSalesData where InvoiceDate < '2020-07-01') Statement below is a column I use for an sql query.. ROW_NUMBER() over(partition by a.Customer order by MIN(a.InvoiceDate)) as OrderNum I am getting an error while trying to convert this statement. I am not sure how to…
-
Variable Dates (User Defined) and Status Determination
Hello there. Hoping to find a solution to an issue I am trying to solve. Summary I have a series of time entry compliance dashboards. Data is pulled into Domo via the timesheet app connector. My dashboards highlight compliance, based on 7 hours a work day, categorizing into "Full" or "Partial" or "Zero" The solution I have…
-
Can Magic ETL calculate a sum over an entire column?
I hope I am missing something here. I want to simply run the equivalent of SELECT SUM(columnA) from TableB in Magic ETL. However, it seems the only way to do this sum is: 1) add a columnC to TableB where every row in columnC is the same value, and 2) do a Group By aggregating on columnC. Is there a better way in Magic ETL?
-
ZIP code leading zeros being removed in dataset
I have a dataset and one of the columns is ZIP codes. The leading zeros are being removed when uploaded to DOMO. Any way to change the column's formatting or to prevent the zeros from being truncated?
-
Is it possible to use a Domo card as an input for an ETL
Hi, I would like to create an ETL using a Domo Card as an input dataset. Is there any way to do it? P.S.:The card contains some beast model window function calculations which I am not able to replicate in the magic etl. So I am thinking it would be easier to use the card data as an input for my ETL. Thanks in advance.
-
Null value
Very basic question here, trying to create a calculated column returning "TRUE" if another column has a value and false if not - neither of these are working. I assume my language is not correct here? Thanks case when `Intro Call Held Date (Cleaned)`= value then true else false end or case when `Intro Call Held Date…
-
Checking the existence of an entry in the immediate future date and counting in future date
Hi All, Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the number of entries that have gone off the list…
-
column referenced isn't found but exists in the connector
In ETL formula tab I am trying to rewrite a column name and am getting an error that says the column referenced isn't found. The column name pops up in the list of available columns to re write but then errors when I try to validate the formula. Is there any fix to find this column?
-
Comparison of data to the immediate previous date for every consecutive date
Hi All, Hope you are doing well!..I am trying to build a Magic ETL workflow to calculate the number of entries (combination of sno and mmodel) occuring new in the current date of reference in comparison to the immediate previous day.. Please find the sample data below: Input table sno mmodel snapshot_date…
-
Reusable dataflows / ETLs
We're trying to come up with a good strategy for dealing with large datasets that contain mostly historical data but need to undergo complex transformations in a dataflow or ETL. For example, our user activity log (currently about 7M rows) needs to be joined to several other tables and then we need to segment the activity…
-
Create a URL with CONCAT and variable in the middle.
CONCAT("https://b2b.officedepot.com/rest/ODServices/api/product/priceAvailability/xml?zipcode=XXXX,"&sku=", `VARIABLE` ,"&billto=XXXX) I am trying to have a cell value where it shows VARIABLE . Everything else is part of a constant URL. I just can't get the variable To be in the middle of the URL. I have worked with others…
-
Is AS valid in Beast mode?
Good afternoon, Can AS be used in Beast Modes to create aliases? It doesn't seem like it but I might also be super bad with SQL. Thanks! -Joe
-
Beastmode. How to create a CONCAT URL with several variables?
Want to build the following link on a card =CONCATENATE("https://b2b.officedepot.com/rest/ODServices/api/product/priceAvailability/xml?zipcode=",`CONSTANT1`,"&sku=",`COLUMN`,"&billto=",`CONSTANT2` ) The URL is a constant as well as "CONSTANT1" and "CONSTANT2", but also need "COLUMN to change depending on the value of the…
-
Rolling Average for Each Category
I have a dataset containing total monthly units by month and by person. I'd like to calculate the 3 month rolling average for each person. How should I group the data so that the rolling average calculation goes by person and not by date?
-
Calculating existing delays , Off delays and new delays on manufacturing data
Hi All , I am new to Domo and I am trying to create a beast mode for the following type of data... I have a manufacturing plant data on a daily basis and specific vehicle (denoted by sno) belonging to a specific category (mmodel) that can go on a delay due to some issues. Now my Input tab has the snapshot of this data on…
-
Can I make a "mother row" to assign values from?
We are a music library company, and as such a large amount of our income comes in as small royalty payments that add up incrementally. The information comes from 15+ different collection sources, and in general the only thing consistent between them all is the track title. That means that the information from the royalty…
-
Replace Text Regex - IP Address Regex
I need some help. I'm trying to find an IP address in a text string and pull the IP value out. I was hoping to use ETL 2.0 formulas, but to start was trying String Replace with the REGEX feature. REGEX FORMULA. (my IPs start with 199.15): 199\.15\.[0-9]{1,3}\.[0-9]{1,3} REPLACE WITH: (I'm trying to pull the first match) $1…
-
Rolling Average Calculation
I have a set of monthly data and would like to calculate a 3-month rolling average. For example: Jan = 30 Feb = 32 Mar = 34 April = 36 Rolling average for March would be 32 ((30+32+34)/3), for April would be 34 ((32+34+36)/3). It is probably something fairly simple, but I'd appreciate your help. Thank you.
-
How to set the time for hourly data refresh?
I have a dataset that refreshes every hour. However, it seems to follow the time when I initially created the dataset. For example, I first uploaded the data at 9:20PM and now it refreshes every hour at 20 mins. How can I change it (Say 9:10pm) ? Thank you.
-
Right way to utilize string operation on long date.
I have a long date that I have converted to a text. Now I want to use the string operations tile to cut off the tail so I can have a nice clean date. the way it looks now in a string format is: 2022-02-24T12:08:34 What I want is: 2022-02-24 What I have been trying is: But it leaving the date unchanged and that timestamp at…
-
Filter a DataSet with values from a Google Sheet?
Hello, I have a dataset that I would like to be able to dynamically filter based on values in a Google Sheet. This feels like a fairly simple MagicETL to me but I'm stumped at the moment. In short, the PROD dataset is good to go and I would ideally be filtering one column from the PROD set based on values in one column in…
-
Domo not capturing all data despite data range being correct
Hey - Appreciate your help. I have a spreadsheet that is about 380 rows long The first 370 rows are individual locations, and the last 6 rows are "Region" rollups for the 395 centers above. I made sure the data range includes through row 379, but these don't show up when I try and filter by column A, only the first 370…
-
Right way to utilize string operation on long date.
I have a long date that I have converted to a text. Now I want to use the string operations tile to cut off the tail so I can have a nice clean date. the way it looks now in a string format is: 2022-02-24T12:08:34 What I want is: 2022-02-24 What I have been trying is: But it leaving the date unchanged and that timestamp at…
-
Best Practice: ETLs Vs SQL ETLs
We are trying to rebuild a tool Domo Tech built for us called Domo Stacker. Its main purpose is to aggregate many datasets together. Our current datasets are 250+ recursive ETLs that output a dataset that is growing (think: historical + appended new data) for four buckets of categorized data (four separate outputs when…