-
Holidays between two dates
Dataset A contains date_start and date_end. Dataset B is a calendar with a field called is_company_holiday. I need to figure out a way (in Magic ETL 2.0) to create a value in Dataset A that tells me the number of holidays between date_start and date_end. Anyone got any good suggestions? Thanks to previous threads that have…
-
Existing dataset as output
Hi, I was editing my ETL and removed the output dataset part and had saved by accident. How do I add the output back in as it seems to just recreate a fresh dataset with the same name rather than write to the existing one. Thanks
-
How to create a column which SUMS based on the grouping of another column
Hello, I am trying to write an ETL which sums a value based on the grouping in another column (invoice number) which yields a column with the total quantity for each invoice. i have attached a simple table as an illustration of what i am looking to achieve. I do not want to write a formula where i have to continually…
-
Combine columns except when null (i.e., skip null)
Hi there, Is there a way to combine values in multiple columns except when the value is null? For example: This is a small example, I need to combine 8 different columns with a separator without ending up with: cat, , , , , orange, . I'm essentially looking for a DOMO equivalent of TEXTJOIN() in Excel. The values don't…
-
Equivalent of IFERROR(VLOOKUP,IF(RIGHT(- in Magic ETL
Hi Guys, Hope all is good. I am using Magic ETL and trying to create a flow replicating the current logic of my Excel file. In Excel I have 2 datasets. On the Main sheet a column is categorizing using the formula =IFERROR(VLOOKUP(value,range,exact Match),IF(RIGHT(Category,2)="AB","Above","Below)) Now in Magic ETL data…
-
Is copying action nodes from one dataset to another possible yet?
This idea was mentioned towards the bottom of this thread by @MattGo and then @StevenC mentioned that it was a good idea back in 2017. Would save us a lot of time and add a lot of value.
-
Fixed Functions for Identifying the First and Last Connections for an Entity
Hi all. I built the following beast mode to identify the first and last connection for an entity. It validates and works when I lay out the connection ID and the beast mode in a basic table, but it doesn't really work with any other visuals (I get error messages), and when I tried to use this to build a net new column in…
-
Is there a way to combine/transform a Date&Time column into Domo?
So I've built a data set in another program, Midas, that I created a workbench job for and pulled it onto Domo. It has columns such as "IP Start Date" and "IP Start Time" and similar for discharge times etc. An end user is wanting to see patients broke down by Admit date, military time, and specific length of stay like…
-
Combining Rows of Data with a common attribute
Hi, I have a dataset which is product data cross multiple years. In some cases the same buyer bought the product in year 2022 and year 2021 and I want to roll this up into 1 line. How do I do this automatically, matching on the word 'Buyer C' (because it's not feasible to manually match them with the size of the data) Any…
-
Trimming and converting a Date/Time Field
My goal is to filter all times between the hours of 9PM-5AM, but the data in my field looks like this: 2021-12-31T21:20:49 How would I trim this field to get the time exclusively and possibly convert it to 12-hour format? Thank you for your help
-
Making a bucket list of defects
I am trying to make a bucket for sentence long defects and put them in a column that is easier to filter by and group the defects into a certain defect code. I was able to get this working on a beastmode perfectly but when I try to add this in my ETL it gives me the code Failed to turn value string into Boolean. Here is…
-
Transforming 6 digit integer to a date when bad values are involved
I have a dataset with chunks of bad data in it. To get a MFG date I pulled the first 6 digits of the serial # to get a "date" in an text format. if the data is correct it should read out the correct date, but in some cases the data will have 06B378 or other letters or texts that should not be in there. How would I go about…
-
Can we export output data from a workflow in ETL?
Hi All, I wanted to know if there is an option where we could get the output data as an Excel file(like a downloadable excel file) or even have the feature where the output data can be uploaded to a service/server from the workflow or at least have the output in format of a downloadable excel file.
-
How do I convert a string to a date within Magic ETL?
I've seen questions asked about converting string to date, and answers were all given to use Beast Mode or add a separate field. Is there a way to do this in Magic ETL? I want to just have all the cleaning done in one place but can't find a way to do this in either version of Magic ETL.
-
How do create a formula that add to line items?
My data is broken out in the statement of cash flows below is how my data is structured. If I wanted to combine Loan and revenue, add credit balance by quarter. What would be the best way? I also want to include a date field for card functionality. Account Q1 Q2 Q3 Q4 Credit 10 15 20 25 Loan 20 25 30 35 Revenue 30 35 40 45
-
Building a filter/fxn for ETL to pick the earlier of two dates when applicable?
I have a date set of patients that went through my healthcare organization. Some patients begin there encounter with an observation portion of their admit and some start directly as an inpatient. The patients that have an observation portion will have a observation start date and time in the data set and an inpatient start…
-
How do I group by a integer but take into account both instances of the integer?
My company has a demographic table like the one below where we keep true or false info on borrowers. When the loan has two borrowers, we create the same loan id twice in our system. I am working to create a minority table but I have to take into account BOTH borrowers. If either row (1,1 - 3,3 in this case) has a true in…
-
Breaking down bundles into individual items
Hello Everyone, I am currently pulling in sales data from shopify. We have a core range of about 20 products and another 20 or so bundles which are made up of those core products. I am hoping for some assistance with how to break down these bundles into its components (core products) as we would love to see how the core…
-
Count function on a column to get total for two areas
I am trying to do a function to give me two number totals for locations by product line. The item number is a sublevel of the product line and I want to see how many of: for example 'Electric Cooktops' are in the red section and how many are in the green section. It might be an aggregation problem but the rank and window/…
-
Calculating hits per minute by timestamp and Parts ran
I am trying to calculate a live hits per minute by machine based on total parts ran per the difference between the start time of the day and the most recent timestamp. I am having trouble piecing together the correct formula to go about this. Does anyone have any suggestions? Thanks
-
Split varying full names into First Name, Middle Name, and Last Name
Hi there, Back with another question. I have a Full Name column in varying format: Alex McCandless Alexander David Thoreau Bobby Del Rio Cameron Diaz Camilo Carter Dave O'Donald Jack E. Topez ... and I need to parse this into three columns: First Name - the first word of the string, seems easily accomplished with the split…
-
Magic ETL hydrating error
Hello, Wondering if anyone has encountered an error like this before and know what it means? (other than that the action is thirsty)
-
Calculating Headcount based on Labor Hour Report
I am trying to get a HC for each department from our labor hour reporting data. I was going to use a count function by employee name to be able to pull the number of workers for each department. The challenge is for example: Date Employee Department 10/12 Jon Fabrication 10/13 Jon Fabrication How would I eliminate the…
-
Return most recent row/date
Good morning. Been running down the rabbit hole for a day or two on this and trying different solutions from previous Domo questions without much success. I have a sheet that has multiple entries for each equipment number and in the end I would like one row for each piece of equipment that is the most recent EVENTTIME.…
-
Beginner - managing a wide table, creating sum of..
Hi there, newbie here. I have a wide table with a column format a bit like this: name, question1, OPP1, EXP1, ACH1, question2, OPP2, EXP2, ACH2 I will need to make calculations for each row on the values (integers) grouped by OPP, EXP, ACH. I thought to create new columns with the sum of OPPx, EXPx and ACHx (where x…
-
Flow for dates
I have the following table: Id Service Start Stop 12 Mobile Jan May 13 TV Feb Sep what I want to do is reshape for a line chart to have counts of accounts for active services: id date service 12 feb mobile 12 march mobile 12 Apr mobile 12 May mobile 13 Feb tv etc…
-
Create beast mode dates
Hi everyone, help needed! Here is how my table looks below. What I want to show in Analyzer is the count of accounts of these services over time. My problem is how the dates are shown here. Any advice on what I can do? I thought about creating a calculated field to index dates but not sure how to do it. Any help is…
-
Domo performance
Has anyone else noticed that lately Domo is incredibly slow? I've been trying to reduce dataset size, but even editing cards, it takes 30 seconds to 1 minute just to open the analyzer. I've also noticed that data sometimes doesn't change when I edit the card, but when I save, close, and reopen, then the data changes. Also…