-
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…
-
Converting Week numbers into date ranges
I've got some quarterly data that I'd like to format a bit. I'd like to change the "week-1 2015" labels (see attached image) to something like "Jan 1-7 2015" etc. I can't seem to find which beast mode formula I could use to accomplish this. Thanks in advance!
-
How to Make a Period Over Period Beastmode Work with Date Filtering
I am trying to create various period over period beastmodes, some for summary numbers and some for the charts themselves. I have variations using currentDate() in the formula - that works on the default view card, but if you add a drill-down or filter by a historical date range they break. Here is a FYTD/FYTD change ($)…
-
Beast mode using AND and OR
Trying to run sales data to determine how many reps have open opportunities within one of the 5 products as well as of those opportunities which ones have status of closed. I am using this beast mode to find whenever the opportunity status doesn't = a status thats closed and the item is PBCS then put a 1 so I can sum/count…
-
Percentage of Total
Hi How do you create a column in ETL that sums an entire column? I am trying to create a card that gives percentage of total, from looking at other posts the way to do it would be to create a column in ETL which gives the Grand Total of an entire column, example attached, then create a beast mode in a card. But I can't…
-
SUM for sales when there are multiple prices
We have a few places that we receive pricing for a product, so to account for it in our sales beastmode I have been using the below formula but it sometimes provides numbers much greater than the equation should provide. An example would be a quantity of one * a price of 32 has (incredibly infrequently) given a price…
-
How To change the order of transformation in dataflow?
I wonder if it is possbile to change the order of transformation on dataflow? If not how can I ask for this feature?
-
Average Basket Size
I am looking for a way to create an average basket size by date Assuming I have data set that looks like this: InvoiceDateSKUSold Price111Jan 4Product A10111Jan 4Product B20112Jan 4Product C5113Jan 4Product A10113Jan 4Product B20113Jan 4Product C5114Jan 20Product A10115Jan 20Product A10115Jan 20Product C5116Jan 20Product…
-
MAX or SUM With Group BY ID
I'm looking for a Beast Mode formula to create a MAX calculation by ID in my table. My resulting column should look like column B below. I already tried a formula with Distinct and MAX but while the formula was valid, my attempt still failed at producing desired results. Keep in mind that in my scenario, column A is a…
-
Use "Yesterday before 2pm" in a case statement
Hello all! I'd like to use "yesterday at 2pm" in a case statement. How do I write out that condition? The case statement would look a bit like this. Case when 'date' < "Yesterday at 2pm" then 'yes' else 'no' end Appreciate any help.
-
I'm trying to calculate number of days without weekends, can someone please help?
Hi! I'm currently trying to calculate the number of days between the creation of an application and the current day, but I need to subtract weekends, does anyone know how? I do know I can do it under beast mode DateDiff, but I don't know how to remove the weekends. Thanks you!
-
How do I group (summarize) only certain data?
Hi, I have Salesforce data that comes from a couple of different objects. Primary is Opportunities but it's also pulling some information from Accounts. The data would look like this: Account Name Opportunity Opportunity Value Account Value John Doe Opp 1 50 125 John Doe Opp 2 65 125 John Doe Opp 3 30 125 Jane Smith Opp 1…
-
force date sort order
How do I get these dates to sort chronologically? I have the date field in sorting with no aggregation but it's not working.
-
convert string to whole number using magic etl
Hello, I am trying to convert the datatype of a column to whole number which is having a string datatype using magic ETL and getting attached error. Source for the data load is from CSV file and i am loading data using workbench. Also tried changing the datatype in workbench which also could not complete the run. And when…
-
Compare rows and delete if one of the fields do not match
Hello, I have data like this. 1, red 1, blue 2, purple 2, purple 3, yellow 3,red I'd like to edit the data to show the data like this: 1, red 2, purple 2, purple 3, yellow So I'd only like to only keep duplicates if the first and second field each have a match in the data. Any ideas? Assuming it is ETL somehow but can't…
-
Magic ETL - filter with not in
Please forgive me if this is a stupid question but until this point all of my dataflows have been in mySQL or Redshift and I am trying to utilize the Magic ETL where possible looking for performance gains. Common scenario is to get a list of accounts and use that in a where clause to exclude those records from a result.…
-
Summing a field based on filter criteria
I am trying to create a summary number showing the percentage of the total that is "open" (as opposed to closed lost or closed won). I have been playing with beast modes and have not been able to figure out how best to display this. As an example, we would have 400 accounts that we are displaying trends in a card, but the…
-
Hour-over-Hour for the past 24 hours
Hi, I have been trying to figure out how to graph performance of applications for each hour in a rolling 24-hour period (and once I get that then I need to do it hourly for a rolling 30 days). My database behind the scenes is a MySQL database where I added a calculated column so that I would have just the date and the hour…