-
trouble with str_to_date in beastmode
I'm having complications formating an existing date column to my liking with the beastmode function. The existing date column is currently formatted dd mmm yyyy hh:mm:ss AM or PM. This is in a table chart, the chart calendar is using the date column I just referenced, and is graphed by none. Right off the bat, it is weird…
-
Converting text to date in ETL
Hi all, I'm trying to convert a text into a date field in magic ETL, and I'm running into errors and unexpected outputs. Screenshots below. When trying to just convert the (numbers as text) into dates, I'm receiving the error "An invalid date was found". When I conver that column first from text to numbers there's no…
-
Beast Mode to change value in Column A based on Value in Column B
Hi there! I have a table that includes a Site ID column and a Site Name Column. I want to change the Site Name when the Site ID = x. I have the following code which works but it creates a new column but I don't want a new column, instead I want to change the Site Name in the existing site name column. Many thx in advance!…
-
Historical Database I inherited
Hi all. I'm dealing with CRM data, and have a snapshot of all of our data that is appended to a full historical daily database. This is done in a SQL transform, but I am completely unable to figure out why the input data is being appended to the output instead of clearing it. I am completely dumbfounded. I'm attaching…
-
Beast mode as filter
I created a beast mode to calculate $/visit, in my table chart, I wanted to show $/visit when it is great than 0. Any one has an way to work around since beast mode cannot be used as a filter? Thank you. Olivia
-
Counting characters in ETL?
Hi there, I know there's a way in Python to count characters similar to LEN() in Excel, but I don't know Python at all to be able to use it as a tile in the ETL. Can someone help me figure out how to add a new column of character count to a dataset using Python? Thanks!
-
creating a formula for quarters based on value received at month level
for quarter 1 where month = jan , feb , march if data = 1 , then sum = sum+1 else sum = sum+2 basicially i want to write a formula where i need to check that if there is data in months, then add 1 , else add2. and this calculation is based on quarter level. I dont know how to write this in DOMO case
-
Issue changing string date to date in MYSQL ETL
I'm working on a query where I'm fed in a month name and need to change that into a date. I've formatted the string so that it looks like '2019-11-01' and such, but when I try CAST, STR_TO_DATE, DATE_FORMAT or anything else, I get a blank into the field value. I'm not seeing any errors. Any thoughts on what I could be…
-
Average Data before Combining in ETL
I'm trying to create an end result of comparing Rain to Sales by Day. I pulled Rain data from the NOAA connector for the entire city, which ends up breaking it out into individual Stations across the city. The issue I'm running into is that when I combine this with Sales data for the city, Domo adds the total city Sales to…
-
Split dates in single column into multiple columns
I want to take a column that contains dates and split it into at least 2 columns to find the date difference between the two. So, for example, I have a client ID and a transaction ID that are unique. The transaction ID does not repeat, but the client ID does based on their purchases. My date column is their purchase date.…
-
Adding Days to Date
I am trying to add 7 Days to my date in SQL Transform. When I simply do Date + 7, the date is larger than the end of the month it shows as a larger number(2019/08/30 becomes 20190837). How do I add days to date. Select date + 7 from table
-
Sorting question, why are values of zero counted highest?
Hi, I am sorting a table of highest to lowest based on yearly sales. An issue I have is that sales reps with 0 sales are always sorted to the highest position. This is annoying because I am unable to place them at the bottom, even by creating beast modes. I am not sure how to exclude them from the data set I would either…
-
Magic ETL Exclusion
I'm looking for an efficient way to only keep contacts that appeared in the year 2018 and exclude contacts that appear in the year 2019 AND 2018. It's possible for a contact to appear several times in the same year, so I cannot simply use the Remove Duplicates tile. In Magic ETL, how would I do the equivalent of: SELECT…
-
combine multiple fields from one table into one main field
hi everyone! I'm working with a dataset in which every new transaction creates a new 'transaction date' field as oppose to keeping the dates within one field. see sample attached. I'm trying to figure out how to combined the fields 'transaction 1 date' thru whatever the number of transactions may be, into just one…
-
Multiple Level Aggregation
Hi all, Running into an issue when trying to do multiple levels of aggregation in a beastmode calculation (not sure if it is actually possible as a beastmode). I have a dataset that looks something like the below with data at the item-location-day level and a servicing DC associated with each store. What I am attempting to…
-
Importing a value with leading zero
Friends, I am importing a simple dataset, and the leading zeros are being truncated. Example: I want: 000001 But end result is: 1 Does anyone have any suggestions about how to work around this? Thanks.
-
Table Cumulative Sum
HI, I am trying to get my data in the left to look like my data on the right. I've seen some similiar cases but the once I see are moving into a line graph and I need these to be in table format. Current WantFIscal Month and NameLY $TY $Plan FIscal Month and NameLY $TY…
-
Rank & Window Function
I wanted to create two static fields for two different tabels coming from WB. 1.One is ID- unique # for each row. 2.Source- which differentiate which data belongs to table A and Table B eg : Union ALL both tabel A & B final o/p shall be : ID| Product| Class| Source 1 |AAA | C | table A 2 |ABC | D | table A 3 |AAA | C |…
-
Using Explain Plan to check Mysql query performance
Hi, I am trying to run Explain plan on my queries to check query performance. When I use the SQL transform I get a message saying the statement is valid but it does not show any output. When I use the Table transform it throws an error saying it needs to be a SELECT statement. So I was wondering is there any alternate way…
-
How to add the unique numbers to every row of the output?
I have dataflow which consists of ETL o/p( table 1+Table 2) as i/p in sql. and further this I need to send it to another department, so they can fill in the blank fields. Thy will be able to fill in the blank fields only if I add the unique Key to each row of the o/p 1.I did add the Index of General type- looks like it did…
-
How to export the data from SQL data flow ?
I have created dataflow, and want to send it to another department to put their inputs, so how can I export the data from SQL dataflow ?
-
Best way to return earliest of multiple date fields in Magic ETL
I have a dataset that has five dates... I want to add a new column that contains the earliest of those five dates. That is easy to do in a MySQL data flow... but I can't figure it out in Magic ETL (I'm just starting to use Magic ETL more as our dataset sizes are growing and Magic ETL seems to be "faster"). Any help would…
-
Select rows from a datset based on Date
Hi, I have a dataset with one column as Date. Now I want to create an ETL process to create a new Dataset from the existing one based on rows which correspond top 10 days in the Date column of the existing data. I checked on the available options for ETL but failed to see any direct way to do this. Only option that is…
-
Green Transform Check Marks Gone!?!?! WHY!?
Wondering why the green check marks that let you know a transform had been run have been taken away. Was this on purpose on is this a glitch? Those things saved me so much time I don't understand why they would be removed. AM
-
ETL Select Columns "Shortcut"
Hello, I work with Datasets with high counts of columns. When I create ETL's where I have to Select Columns, I can be going through 150+ columns at a time to choose the columns I need. If I only have a handful to add, scrolling through the list and adding one by one is not really an issue, but when I need to add 30+, it…
-
Grouping Dense Rank into Weeks
Hello, I am trying to build a product lifecycle comparison using first invoiced date as day 1 and then consildated all of those into multiples of 7, to get sequential weeks. For instance; day 1-7 would be 1, 8-14 would 2 and so forth. Mind you, I am using dense rank. Any help would be amazing.
-
Identify Multiple Different Matches in Same Column
Hello, I'm looking to be able to build cards that compare different products that are tied to different opportunities. The data currently comes in via one column, and if multiple products are on the opportunity, it is comma-deliminated. OPPNUM Products 12345 Product1,Product2,Product3 I've tried doing via Beast Mode: CASE…
-
How to delete blank rows in a data set
I have a table which I've sorted by the count of a particular column. However, the column that is sorted at the top is one that has nothing but blank data in it. How would I create a beast mode to remove this field. (Please see photo below.) I've tried to create a beast mode that does this: (CASE WHEN…
-
Aggregating a column in Beast Mode
Is it possible to aggregate an entire column in a beast mode calculation within analyzer? (I have found videos explaining this within a SQL ETL, but not as a calculated field in analyzer). For example: In the table below I want to divide the value in the "efficiency" column by the averge of all values in the column. Right…
-
Sum Column Based on Distinct values in another column
I need to sum the quantity in one column based off the unique values of another column. I cannot remove the duplicates within the ETL because there are other columns with greater detail that would be lost if done. So i need a beast mode that would give me a result of 50 for the sample data below. Any help is appreciated.