-
Reduce ETL Processing Time
Hi Everyone. I'm looking for strategies to reduce the run time of Magic ETL 2 data flows. I'm working with several left joins in sequence on about 17 datasets of 1 million rows each. Beyond this, the ETL performs no other operations. I'm curious if anyone has experience with these options: Data fusion Adrenaline Redshift…
-
Case Syntax
I am having difficulty understanding the case syntax while using magic ETL. In the current Example, I have a list of properties and coded them to display a text column of the region for each specific property. Unfortunately, there is no address line to be able to parse and use to make the regions so I'm hard-coding the…
-
How to represent how many tickets opened by each individual user?
Hi, I am trying to figure out how to pull 2 datasets together correctly. So, one dataset is Users and the other is Tickets. I want to connect Tickets to Users so I can have a single integer connected to a single user to show many tickets a specific individual has opened. Example: User_Name Number_Of_Tickets_Opened…
-
Only show unique values of a column in table
Is there a way, in analyzer or ETL, where we can only display one row for each unique value of a column corresponding to a key column? For example, I would want this: ID Num 1 4 1 4 1 5 2 4 2 5 2 6 3 6 3 7 3 7 to become this: ID Num 1 4 1 5 2 4 2 5 2 6 3 6 3 7
-
Error filtering Data
Hi, I'm using a filter on a Dataset I which I need that a column A to be different to FALSE and another column, B, equals to FALSE, but it is not doing the filter correctly
-
How to Deal with Multiple Date Fields
I have a perplexing issue I'm trying to solve. I have data coming from several different data sources (all parts of Zendesk), each of which each represent a different kind of user interaction. For example: Email, Chat, Phone Calls These are all related to a master "Tickets" table, so they can be joined based on a Ticket…
-
Avg. Time between purchase orders
Hi, Domo newbie here. I am trying to find the average time between orders. The columns I have are Customer ID First Purchase date Last Purchase date Time between purchases I tried to using the group by function to find the average time between orders, but it seems to be returning the same value as the 'time between…
-
Is there a reliable way to make Magic ETL intentionally fail?
I have an ETL that uses, among other inputs, a Webform that isn't always maintained perfectly. One recent problem involved two rows for the same identifier, with different text in the description column. What I'd like to do is make the ETL fail if something like this happens. I tried doing a Group By on the identifier…
-
Is there way to extract week of quarter from a date column?
The default week() function extracts the week number from the start of the year and runs 1 to 53 or 54. I need to show week number within the quarter, running 1 to 13 or 14 within each qtr. Is there a way to do this using beast mode or using the formula tile in Magic ETL ? Thanks!
-
Automatically update the column value by current month and year in Magic ETL
hi guys, I got a table on below and some columns using month/year as column name. i would like to make an automatically Magic ETL and set a logic condition like below: if the date(column name) < current month and year (let say 08/2022) then all value would be clear as 0 value under the column (01/2022, 02/2022,…
-
Limit on ranking tiles
I added 20 ranking tiles to me Magic ETL, but it fails stating out of memory. I removed 4 of them, and now it runs fine. Is there a limit to the number of ranking tiles you can use? The input is only 2 million records.
-
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 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…
-
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…
-
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?
-
Period over Period in a table
I'm attempting to do a Period over Period in a table, but want to keep the date range to previous week/month/quarter to keep it auto-updating and grouping the data in a particular way. My idea was to create a new column in ETL that is the same as the column I'm trying to do a Period over Period calculation, but from the…
-
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…
-
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.
-
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…
-
Pendo connection: how to add a date column to a dataset?
Hello! I have created a Pendo connection to domo but a date column was not pulled over with the data. I was wanting to add a column to the dataset and put the date that the corresponds with the data but am struggling to do so. Does anyone have any ideas on how to do this?
-
Duplicate data points for Weekly Reporting. I need the most recent day for weekly reporting.
Our subscription data is collected on multiple days throughout the week and creates a snapshot of that point in time. I want to see what that week's total was based on the most recent day. I don't mind removing the row with an earlier start date.
-
How do I create a rate with 1 row of data then filter it by unique cells of another row?
I'm pretty new to Domo and work in healthcare but essentially I have an excel style sheet of data. In 1 of the rows is the list of different admitting providers and in another row it specifies if the patient was inpatient or observation. There is a column/unique account number for every patient in this data sheet. I am…
-
Comparing two datasets for missing sites
Hi, I'm looking to compare two datasets from Smartsheet to identify which sites are missing from one dataset. For example, show me all sites that are in 'Master Schedule' but aren't on 'Secondary Schedule', both sheets have identifying numbers in column 'URN'. What would be the best way for me to do this? Would I join…
-
Replace text and value mapper failing
I have a couple data flows that use replace text or value mappers to remove certain text characters from a number column to then convert to an integer. Examples of text we are finding in our source data are "#DIV/0" and "--". However, when those characters don't exist at all in the source data, the replace text tile fails…
-
Replicating SQL statement in Magic ETL Formula tool
Hi All, I am trying to replicate the following SQL statement in the MagicETL formula tool.. (SELECT MAX("FISCAL_YEAR") FROM table WHERE "DATE_VALUE" = CURRENT_DATE) AS CUR_FISCAL_YEAR In the Magic ETL tool I am giving the name of the output column as CUR_FISCAL_YEAR and Write Formula as MAX(`FISCAL_YEAR`) WHERE…
-
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…
-
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…