-
Group By "Concat Distinct" Now Live!
I wanted to make a quick post to let you know about an update we made to the Group By tile in Magic. In your instances, you should now see the ability to choose a new options when defining how a column should be aggregated in the Group By tile: Combine distinct strings separated by , This allows you to create an aggregate…
-
Regexp Match not validating
I have a magic etl with a Formula using REGEXP. It doesn't validate but I am wondering if I can even do what I'm doing. I want to extract all values between 2 string characters. Field Value (snip) DECODE(TRUE, IN(LTRIM(RTRIM(deposit_account_type__)),'410'),'Brokered', IN(LTRIM(RTRIM(deposit_account_type__)),'411'),'CDS…
-
Data transformation Question
I am working on a report where the data looks like below (sample data): Services : JAS, GAF and YAG fall under the profit center "ABC" and HIG and OPD falls under "XYZ" Date Profit Center Service 1/1/24 ABC 1/1/24 JAS 1/1/24 GAF 1/1/24 YAG 1/1/24 XYZ 1/1/24 HIG 1/1/24 OPD I want my final output to look like below, there…
-
Dynamic comparison values
Hi Team, How to compare two dates values comparison in dynamics with examples coding Thanks, Raja Parthasarathy
-
Marking Duplicates So I Can Filter Them Out
Good afternoon, I am trying to mark rows that match up with each other as duplicates so that I can filter them out and only have the truly unique records left. Example So after I mark and filter the only record left will be the third one that doesn't have a match. What is the best way to do this? Thank you in advance
-
Question on Expanding Data to Display Unique Values
Is there a way to take the data that has a start/stop year (Column A/B) and have it expand while retaining the associated notes from Column C? The data is in Domo, but can manipulate in excel. The goal is to convert columns A-C to E-F. Not sure which platform makes most sense to make this happen (excel, access, Domo).
-
Combining two datasets based on two potential fields
Hi, I have two datasets. One has pacing data, the other has monthly data. All rows have unique IDs, however the pacing data may have either an ID from the old system (legacy ID) or the new system (new ID). Pacing: ID 1 2 3 4 11 Monthly: New ID Legacy ID 10 1 11 12 13 2 14 3 15 How would I go about joining these two…
-
Count items within a cell
Looking for a formula that can count the number of items in a cell, that are separated by commas and I am stumped. So in the example below, row 2 has 3 items, row 5 has 0, the other rows have XX items. Any suggestions?
-
can someone please help me for this question of magic etl
Magic ETL Activity Using the DataSet in Domo you created using Workbench in the previous activity, 1. Use Magic ETL to parse the currency code from the "Transaction Amount" column and separate the amount and the currency code into separate columns. Submit: A screenshot of the output of the data showing the different…
-
Flag percent of change when it goes negative
Hello, as the title says I am trying to figure out a beast mode or add an additional column in a view/etl to flag a member when they logged in fewer times this current month compared to the previous month. I manually upload the month's numbers at the end of the month so we are measuring trends and wanting to reach out to…
-
Custom Chart - Customizing the Legend Values / Displayed Names
I have a custom chart that lists all the counties in our state. When applying the custom chart to data, the legend populates the values. I want the legend to populate the names. Can this be achieved? The first photo shows how the legend is showing counts of the FIPS (counties) values. The second photo shows an example of…
-
Join
Good day, can I ask for your help. I'm joining two datasets, one from a dataset view and the other one from an email connector. My join keys are two text columns, specifically an email address. There should be a match but I'm not getting anything. I've already tried using the String Operations tile to trim both spaces on…
-
Getting the Avg Days, Avg Hours, Avg Minutes
Hello. May I ask for your help in getting the average days or average hours or average minutes between 2 columns of date + timestamp data? The thing is, the average could be 'days', 'hours' or 'minutes', depending on the values of the 2 data columns, so we want to show the output similar to a timestamp format to be…
-
Join Issue
Good afternoon! I am having a join issue where only my inner join is working, but I would like to either a left outer join or a full outer join. I am trying to join 417 rows of data to 15 rows of data. Thank you!
-
How to create a recursive dataflow with multiple unique dimensions?
I am working with a dataset now that several unique components, including date. Within a given day, there are about 12-15 new records produced based on these dimensions. I tried creating a unique identifier that concatenates these dimensions (including date) with the hopes of creating a unique ID that will distinguish old…
-
Multiple singe day events grouped as single consecutive day events
I am trying to figure out a way, in Magic ETL, to take multiple single day events and group them when there is break in consecutive days. Example, if the results show the 1st, 2nd, 3rd, 5th, 6th, 9th, 10th and 11th, I need three results, not 8. I want to see begin and end dates of 1st-3rd, 5th-6th, and 9th-11th. Any…
-
Magic ETL - Sum of values in a column
In my dataset i have a column "A" and there are numbers in that which represent spend. I want to create a new column in which i want sum of all values in A to be available in the new column in all rows. Eg. If sum of all values in column A is 125, and I have 10 rows, then in the new column "B" I want 125 in all 10 rows and…
-
Vertical Faceted chart not sorting correctly
I'd like to create a faceted chart that shows revenue for a subset of products, comparing multiple months against each other. This would give me a visual representation of changes month over month for my top products, and would promote further investigation if a product's performance one month was significantly different…
-
Transforming an array of custom fields
I have a dataset currently flowing in from MongoDB which is essentially populated based on custom fields that are optionally set within the source. Each element in the array has the name and value of the custom field (ie. customField<0-N>_fieldName and customField<0-N>_fieldValue). The issue is that it's not always the…
-
Beast Mode Calculation partly displaying on the card.
Hello, I need some help. I have made some calculations in beast mode to convert minutes to seconds & the beast mode runs ok. However when I pull in the metrics & respective stages into the card, only the first stage returns the expected values. The formula for all the stages is the same but only changing the stage level.…
-
Transpose columns into multiple rows
I have this data set: Member ID 23-Jan 23-Feb 23-Mar 23-Apr 22-Jan 22-Feb 22-Mar 22-Apr 123 33 41 23 23 32 32 34 34 456 4 15 15 15 6 6 2 2 789 2 4 8 8 8 4 8 12 111 24 24 26 26 27 22 27 27 222 3 12 12 13 14 14 12 12 333 2 1 1 444 2 2 2 5 6 8 10 11 The date columns represent the amount of logins to their account by month. I…
-
Common Practice Joining
Hi, I am running into an issue where I am trying to join two datasets. Joining on a Unique ID is not an option as whoever set up this dataset decided not to include one. My next thought process was to join on date and name. Here is the issue I am running into. Dataset one has the full name for example Fredrick. Dataset 2…
-
Creating a new column using Formula in Magic ETL
Hello, I have a dataframe with a column named 'Screening Date' in that column are dates and null values. I want to create a new column called 'Completed' that looks through 'Screening date' and if there is a date in a particular row I want to Write "Yes" in my new column. If the 'Screening date' column is null I want to…
-
resursion
i have a large dataset that is too large on the SQL source to bring in monthly so I have to bring in daily. So, I need to find a way to keep a few months of data in Domo. What would be the best way to do this? Should I use recursion? Is partitioning another option? I'm not too sure how these work. Would someone be able to…
-
User-Defined Functions (for Beast mode and Formulas)
It would be great if DOMO could add user-defined functions as a feature. Is there any discussion of this becoming an added feature? It doesn't seem like it would be very complicated to implement, and won't make the software any less user-friendly for users who are not developing their own functions.
-
Create A Beastmode filter that Shows Prior Year Sales in January, then switches to YTD starting Feb
Hi - I'm looking to create a beastmode that is a filter - what that filter would do is display 2023 sales in January 2024 (excluding January), then starting February 2024 would switch to YTD. This stems from our YTD cards going blank at the beginning of the year. It is important to keep the cards YTD and to only display 12…
-
How to update field if value is different than existing?
Hello, I feel like this should be straightforward, but I can't figure it out. During our master ETL, I have a lookup sheet that adds fields to the dataset (e.g. Account Owner, Region, Account tier (A, B, or C). However, if an account owner changes and I make a change to the lookup sheet, it's only updating new records…
-
How do I get future data being populated to current year?
I have a requirement to show Next fiscal Year data into Current chosen fiscal year. So if user choses , FY 23, it should show data for Fy24 in 1 column. Please see below: If FY is selected, then monthly data is shown for current fiscal year. and similarly NextFy data (Fy+1) is also displayed like below. Please suggest.…
-
Cloud Amplifier - Incremental Load
I'm Running MAGIC ETL to Transfer Data from Cloud Amplifier dataset (connected to Snowflake view with about 200 million rows) to a dataset without any transformation. Every time it takes about 1 hour 20 minutes to fetch data from Cloud Amplifier dataset (S3LoaderAction) as it seems it is doing full Data fetch all the time.…
-
Beast Mode
Hello All, I am trying to find a better way to code this because I think the calculation may be faltering somehow. Alpha calculation is just a sum of amounts Beta calculation is just case pulling numbers from different fields. Mas field- what I am trying to achieve is simply to divide Alpha by Beta It seems to work somehow…