-
Magic ETL | Remove Duplicates Old Raw
Hi, Assume my data like that I want to become If I use Remove Duplicates in ETL, it only keep the old row. What should I do now ? Many thanks
-
Removing Duplication and Ordering Objects within a Concatonated Field
Hello, Our company offers several different product lines and I am attempting to count how many times each combination is owned. There is a field that comes through to Domo from a SFDC connector with this information, but there is a ton of duplication within as a customer can own multiple of 1 product. Example: I'm…
-
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…
-
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.
-
Snapshot Dataflow
I have a current dataflow that is recursive. I want to make sure the company records that are being snapshotted are. I am planning to use a field "Snapshot Date" which is the current date timestamp on the recursive. How would I go about assuring the companies are being snapshotted daily in the ETL? Would I need to match…
-
Creating new data
Hi guys, I have dataset that gets created as part of an ETL. I would like to take a copy of this data remap one of the columns and then append it to the original set. Something like below. So take the data add a customer Id column default to say 1. Duplicate the data. Remap the customer Id column to something else and…
-
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
-
Snapshot Fields Accuracy
I'd like to create a new dataflow, preferably not MySQL, to show that the fields I intend to snapshot daily are actually being snapshotted. For more context, I have a list of companies with subscription updates that are currently being snapshotted, but I want to ensure that everyday, every company is included. Anyone have…
-
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…
-
How to count distinct based on the sum of another column?
I dataset with a list of "pulls" for each day and the "setter" associated with that pull. I have a calculation through the ETL for this dataset that sums up the total sales per setter for the previous 30 days. My objective is to count the number of distinct setters who have a sum of 0 total sales for the time range…
-
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…
-
Average based on Months Passed and Months left in year
Hello, Looking to take a goal I have already calculated and be able to show how much is needed on average per month until the end of the year. Additionally, calculate the average based on months passed. An example would be a goal of 500 topics posted during the year and currently there are 250 topics posted. I have an ETL…
-
What is the syntax to add a "if contains the following" in my logic
Hello. I want to create a logic that replace those rows that don't have both AP and MA in the name as # and keep untouched those that have. AP is always in the 1st and 2nd respectively. Then MA is in the 5th and 6th. My logic seems to be not working. Can anyone help? --------------- (Case WHEN `sap shipment 1 tariff id` <>…
-
How can I remove all special characters and spaces from values in a field?
I want to remove all special characters and spaces from a field in ETL.... how can I do this? Is there an easier way than having to create an individual replace text line for each specific character?
-
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…
-
Data imputation
Hello, I'm working with a large dataset that needs some data imputation and got some questions around it. I want to filter my data based on location but a small amount of the dataset is null there. Luckily, it is sales order data, so the same SO has many orders to it and SOME of them are lacking the location information.…
-
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…
-
Where can I find the Description when you save a dataflow
I would like to create a card showing the changes to dataflows and the description. I have looked in domo stats and also domo governance and dont see it. I am looking for the comment you add before you save. Not the description of the dataflow that you put along with the title.
-
Joining only where Unique ID matches
I have 2 data sets. Data Set A and Data Set B. Data Set A. Has 6k rows of data and about 50 columns, and it has a unique column ID That can be matched a Unique ID within in Set B. Data Set B. Has 20k rows of data and about 30 columns, this also has a unique column ID. The Unique Column ID will match up on about 1000…
-
how to split up column based on string of charecters
I want to separate out the SKU that is at the end of a url... how can I do this with the following, where anything following "-sku-" is separated out into a new column? I tried using the split columns function and using -sku- as the custom delimitator, but it isn't working.…
-
removing last 5 characters of a value
How can I remove the last 5 characters of a text value in ETL?
-
How to look at multiple strings in one column
Hi, created a Case like below: case when `All File Dates` >= `EncounterDate` then 'Probable' else 'Obtain Op Note' end This doesn't work to full capacity because the dates in the "All File Dates" column are in no real order. See below highlighted row. The output for Op note on file should say 'Probable' because the…
-
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/…
-
Default Values for a column
Hi DOMO Community, I want to put a column on a dataset that defaults to 0. That column will be updated by a python script to 1. But on new data rows being added, it still defaults to 0 without overwriting the 1 rows Note that this will be in Magic ETL
-
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…
-
Month Snapshot
I'd like to build a recursive dataflow, but capture data by month. Any idea how this can be solved?
-
Can you use _Batch_Last_Run_ as the date for a recursive dataflow?
Have a dataset that doesn't have a timestamp that is being imported via connector. Can I use the batch_last_run as the date to create the recursive dataflow?