-
SQL/Beast Mode to extract substrings and output to multiple new columns
I have a column in my data with strings that look like this: Clusters affected:|Finance, Services & Innovation| |Planning & Environment| |Transport & Infrastructure| |Finance, Services & Innovation| |Planning & Environment||Finance, Services & Innovation| |Health| |Industry| |Planning & Environment| |Premier & Cabinet|…
-
Last Date
If I have a dataset and I want to pull the last date information how do I do that?
-
Help on Beast Mode Concat Format
Current I get for a Summary 2711 Meetings and $143043062 Total Est. Budget but I am looking for the $ amount to include comma separators and two decimal. Am using a beast mode of CONCAT(COUNT(`Meeting Identifier`),' Meetings and $', SUM(`Total Estimated Budget`),' Total Est. Budget') and I am a novice and painfully stuck.…
-
Creation of a new variable based on delimiter
Hi, In my Data I have a column name 'Origin_Data' which holds data like 'aa/df', 'bb/saw', 'asd/erw' etc. (character type) Now I want to create a new column say name 'Final_Data' who values will be just the left part of the values of 'Origin_Data' after splitting with '/' - delimiter i.e. 'aa', 'bb', 'asd' etc Any pointer…
-
how to set a MAgic ETL to only filter out past 12 months data
My raw data contains 2.5 years worth of data and I only wanted to have the past 12 months of data for my next step dataflow to reduce the runtime. I wanted to do this in Magic ETL, is there a way to set up the date filter for last 12 months? I only see to set up a date filter for a specific date. Thank you. OZ
-
sum based on distinct in another column
hello, this seems to be straight forward, but I could not get it. I wanted to sum up the cost based on distinct code, but my beast mode does nto work: a sample data, I wanted to calculate sum of cost by distinct code, the sum should be 33. each code has the same cost. this is my beast mode: SUM( CASE WHEN(distinct `code')…
-
Use Magic ETL to isolate the domain of an email address (company.com).
I am trying to use Magic to isolate the domain of an email address yourname@company.com to just company.com. I am open to any way to do it in Magic. So far the only thing I have found is Regex, but when I put it in, nothing seems to happen. I am using /.+@/ This seems to work fine in my tests on https://regex101.com/…
-
Regex_replace doens´t work in MySQL
Hi there, I would like to adjust a column by using Regex: SELECT *,regexp_replace( regexp_replace( `Page`, 'https://www.[^\/]*\/.{2}\/[^\/]*\/', ''), '\\?.*', '' ) AS Townslug FROM `ga_pageviews_per_town` Background info: DOMO loads our Google Analytics urls. Afterwards I need to replace all valid urls into a specific…
-
Case statement using date field
I am looking to do case statement summing a column based off the most recent date in a date column. Below is my attempted beast mode calculation. The calculation keeps returning zero as the result. 4/20/19 is the most recent date in my date column so anytime it finds that date I want it to sum the value in the 'Greater…
-
Chrome auto fill username in DataFlow descriptions
When using Domo in Chrome (after a recent Chrome update), my domo username gets auto filled in the description field in DataFlows. It appears that the input fields must be inside a form element to prevent this issue (link to a Chrome post about it here: https://support.google.com/chrome/thread/1930298?hl=en). Is anyone…
-
Do not Generate Output Table in transformation not available
Hello, I'm looking how to not Generate an Output Table in a transformation ? I did not have this option neither in the transformation neither the dataflow. In lot of tutorial, I saw that a pop up appear with : MagicETL, MySQL and Redshift and then in the editor there is a dropdown list with thoses 3 same options. I did not…
-
Is there a way other than SQL to cross-join two tables (Cartesian Product)?
I know that I can use SQL to cross-join two tables. However, that step is only a portion of my dataflow and I would like to use for the rest of the dataflow. Is there a way to cross-join in ETL or do I need to run my cross-join as a separate dataflow in SQL and use the output in my ETL?
-
Previous month ending balance as current Month Beginning Balance
Hi I am trying to create a beastmode that takes this months balance and subtract it from last months balance also known as this months beginning balance. sum(CASE WHEN `Month ` = '3' then `AR Balance` else 0 end) - sum(case when `Month ` = 4 then `AR Balance`else 0 end) My problem with the above is the two values aren't…
-
how can I dynamically filter out one min score and one max score in a dataset per quarter
basically, its a survey dataset that have a score of 0 to 10.. every quarter, we need to exclude 1 lowest and 1 highest. we may have multiple lowest or highest, we just need to pick one of each for the quarter. for example, for January, we have the lowest of 5 and highest of 9. so we exlcude one 5 score and one 9 score.…
-
Fastest way to append? Magic seems a bit slow
Hello, I am appending 2 datasets that have a only a few common columns to give me and my clients the ability to bring their plans in without having to join them and cause some duplications. With that being said, does anyone know of a faster way to do this beside append in Magic? I know that fusion cannot do this because…
-
Show columns in rows in a table
Hello, is it possible to show the columns in rows in a table card?? I have this… salesdiscountcostEbt5000100030001000100050800150 I wish that… sales50001000 discount100050 cost3000800 Ebt1000150 Thanks!!
-
Show data from current month plus 11
I have been reading the posts about filtering data by the current day/date. They haven't helped my question. I want to show forecast data for 12 months starting with the current month. All dates in the data set are first of the month. Can I filter in the dataset with ETL? Would it be better to filter in the card? If so,…
-
Time taken for ETL Flow
Hi I have a question regarding the time taken for running an ETL flow. I have this AWS object, which has about 90 Million rows and is getting updated by about 10k to 20 k rows on a daily basis. And we have a scheduled update design to run daily. The thing is- we notice that even though only 10k rows are getting added /…
-
Beast Mode Summary Number Total Count for Data 12 months ago
I am using period over period card. I would like to be able to show in the summary the total "lifts" for the current month last year. I'm going to concat that with the sum of the current months lifts. But I can't quite figure out how to get the beast mode to capture only lifts from 12 months ago. Field name = "date" and…
-
IFNULL - Replace Blank with Text
I am attempting to use the IFNULL formula to say if the Data Table column "Worksite" is blank, replace the blank with the text "No Worksite Assigned." I created a valid formula, however, the resulting chart is still blank and does not have the text. IFNULL(`Worksite`,'No Worksite Assigned') Any thoughts on a formula to get…
-
subtracting a negative number
Could someone tell me how to write a beast mode if subtracting a negative number ex. have a freight charge of 199.68 and want to subract a negative 95.36, the calculation is coming out to be 295.04 instead of 104.32 thanks
-
Data Fusion Capabilities
Has anyone used Data Fusions extensively? I've created a data fusion that combines 4 data sets and in total has about 14 million rows of data at this point across about 120 columns. The reason I choose data fusion was to allow for my team to reprocess data if needed should we find bugs in the data we ingest, to backdate…
-
How do I create a script that assigns a value to a new column in MySQL?
I have opportunities that are listed more than once and I need to create a column that ensures its only counted once. Here's a visual example of what I am trying to accomplish Example data: And what I mean to do is assign a value in a new column the first time its encountered and 0 for every time after. So the opportunity…
-
Error starting a dataflow
We have a dataflow which is run daily to update an issue history dataset. The intent is to have the dataflow start when one of the input datasets is updated. Everything works when the dataflow is started manually, but when I select the option to kick off the dataflow when one of the input datasets changes, an error message…
-
Is there a way in beast mode to split a column by a common character?
Hello, I have a a field where the data comes in abc.123.xyz.789. My goal is to create 4 beast modes, where each beast mode will return one segment of the original field. I looked up how to do in a dataflow using substring_index, but that does not appear to be a valid function in a beast mode…
-
Error "Unable to update to DataSet" in MySQL DataFlow
Hi All , I have problem while executing MySQL Dataflow that always failed, the message error is only "Unable to update to DataSet". Can anyone please help or share, whether you have the same issue ? Thank you ^^ Here is the capture : Based on history, try to re-reun 3 times and always failedError Message on MySQL DataFlow
-
PQTD vs QTD
Hello all, I'm trying to create a beast mode calculation to show the QoQ sales growth as a summary number above the chart. For this how will I be able to create a beast mode to get pQTD sales? Appreciate your help in advance.
-
Join twice on a different column
Hello I have 2 fields sh_state and cn_state which are displaying numbers for states where I'm wanting to display the state abbreviations. The field from the abbreviations table is 'id' so I'm needing to join sh_state with id and cn_state with id so it shows the abbrevition on the same row There are 2 tables, loads and…
-
Is there a way to search multiple datasets to see if any are feeding specific data?
I want to see if any of my company's existing datasets include specific data that I would like to start tracking. I know when you open the dataset, you can see what columns of data are feeding, but is there a faster way to check multiple datasets rather than opening each one individually?
-
Beastmode to calculate column based on minimum date case
Hi All, I am running with a error with a below beast mode formula : SUM(CASE WHEN DATE_FORMAT(`Date`,'%m') IN (DATE_FORMAT(DATE(MIN(`Date_INT`)),'%m')) THEN `ColumnA` END) What i am trying to do here is sum column A when the condition for month number is same month number to minimum date(first date) in the column but this…