-
Field that returns 1/0 based on all similar values in the same column
Hello, This should be pretty simple but I can't find the solution. I tried fiddling around in ETL and beastmode, as well as googling, but was unsuccessful. Here is the problem: I am trying to create a column that returns 1 when any unique Job has a PO#. For example, in the second row there is no PO, however the 1st row is…
-
How to give an indicator for a time range
Hello, Can someone give me some suggestions on how to deal with my case: I have a table with project start date and end date, I also know $ of each project, now I need to get the total $ for all project for each month and also compare with the previous month $. This is what I was thinking: get avg $/month for each project,…
-
Download the Schema of a dataset
Is there not a way to download just the schema of a dataset? I'm doing some documentation and it would be really helpful if I didn't have to retype the field name and types of >100 fields. TIA!
-
Monthly Average
Hi, I want to create a Table Chart to show the service level by month for this Fiscal Year. I've created a calculation to get the service level --> (1-(SUM(`Lost Sales`)/(SUM(`Lost Sales`)+SUM(`Sales`))))*100 And now I want to calculate the Monthly Average of Service Level In a line chart, I can manage it with date…
-
Row size is wider than MySql supports. Please reduce the row size or number of columns.
OK so I have this one dataflow, DataFlow A, that feeds into other dataflows, DataFlow B and DataFlow C, that works perfectly fine until I try and add this one additional column to it called Lead Source. When I add this column to DataFlow A it runs just fine, however, DataFlow B and C then fail due to this reason: "Row size…
-
Using two different datasets with ETL calculator
Hi all - I am trying to convert values in a dataset using an exchange rate contained in another dataset. Which function in ETL will allow me to do this? It seems to me the Calculator function only allows one input dataset. Thanks for your help
-
PDP - Policies for Owners / Simulate Policies
We are utlilizing PDP on a dataset which we have duplicated the data to split it into three categories for different levels of PDP. This will allow us to use the same cards for multiple purposes and have the data change depending on the level of access. Issue we are running into is for the owner who by default is in the…
-
lost all beastmodes
Changed the owner on a card and right after that, evey beastmode disappeared. Anyone ever experienced this? Logs do not show anyone else was editing the card, underlying datasets and flows also intact and updating on their normal schedules.
-
how to replace blanks with values from another column
Hi All, I am trying to populate blank cell with a value from another column, i.e. if 'country' is blank then populate with a value from 'country_1' I tried the below, but it doesn't seem to work. CASE WHEN LENGTH(`Country`)=0 THEN `Country_1` END Please help! Thanks Max
-
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