-
Rank Question
I have a list of companys that have different subscription intervals (monthly, annual) and I'm seeing duplicate rows. To mitigate this I want to have one line item for each company, I have tried using Rank and Window and it removes all rows of data. How do I properly get one row for each company using most recent create…
-
Previous day status from snapshot data
I have snapshot data everyday that has customers and their statuses. I want to create a formula that looks back one day and reports the status (previous day status) How would I do that in Magic ETL?
-
Fill in gaps in data
I have rows of data that look like this: Customer: ID: 12 NULL 12 NULL 12 NULL 12 NULL 12 NULL 12 56 How do I fill in the above rows with the customer ID, 56, Using ETL?
-
Values in between dashes
What is the best way to individually parse out a string like this? 1-2-3 want the following: 1 2 3 Open to use either Magic ETL or BM.
-
How to exclude incomplete week in calculated formulas (BM) while using fiscal calendar
Currently am using this formula to exclude current week: CASE WHEN WEEK(Snapshot Date) >= WEEK(maxSnapshotDate) AND YEAR(Snapshot Date) >= YEAR(maxSnapshotDate) THEN 0 ELSE 1 END however, it is excluding Sundays data. (I want to look at Fiscal calendar and it is selected under date settings)
-
Beast Mode for same date check
I have a list of companies and their create date. I want to create a beast mode that checks if a list of companies has the same create date. Any help here? Would this need to be done in Magic ETL?
-
Magic ETL column parsing
Is there an easy way using Magic ETL to create new columns based on existing columns. For example I have an ID column and a Page Name column and want to make each ID and Page Name a net new column to link to other data in the dataset. (I'm using governance Page data) Any ideas here?
-
MySQL
For this question: Join together two DataSets with MySQL using the name, year, and gender columns and include appropriate indexing. Not sure what to index here, is there any additional resources around indexing? The course doesn't go over it explicitly and this link doesn't either:…
-
Field Use Export
Is there a Governance connector or way to filter to a dataset and determine which columns are used in cards/beastmodes?
-
Best practice for removing historical values
I have a snapshot dataflow that has captured bad values. I only need to filter out those values that don't match the current format. What is the best practice for this?
-
Hyperlink in an alert message
Is it possible to add a hyperlink in an alert message. I am having this alert hit slack and would like the ability to include a card hyperlink.
-
View query of tile
Is there a way that you can view what the query would like after you use a set up a tile function in Magic ETL?
-
ETL Blank values help
I have 2 datasets I am appending together and want advice on how to handle null values. For example, one dataset has ID,Name, CustID and one is just ID and CustID. How would I use ID in order to fill in another column with 'Name'. For example: Looks like: ID Name 1 John 1 (Null) Want to look like: ID Name 1 John 1 John
-
Domo bug? from Excel file import
When I append data on a Excel File export (same structure) the valid ID's in the ID column are errored out with ERROR:#N/A Right now its at 58,000 rows, is there an issue here? Wondering why its erroring out those ID's
-
How to count status changes in snapshot data
I have data that I snapshot daily. It has employee name and employee status. Once the status gets updated, it remains that way: ex for one employee: 1/1 Name: active 1/2 Name: active 1/3 Name: active 1/4 Name: Inactive 1/5 Name: Inactive 1/6 Name: Inactive 1/7: Name: Inactive At the card level, I would like to be able to…
-
How to remove whitespace after a full name in ETL
I have a name column with a full name: example Bob Dylan and am joining to another dataset by name. I'm having issues and believe its due to extra white space after the last name. I tried using Replace text, regEx, \s, but that just removed all white space creating BobDylan. Any thoughts here?
-
Simple Reg ex help
I have rows that look like this: Interview with Bob Dylan - CSM and want to parse just the CSM. I tried using Replace Text file, selecting reg ex and using this: -.* but it returned the former portion. Any recommendations on what reg ex to use?
-
Average of a difference in Timestamps
I have two timestamp fields: completed at and delivered at. I want to find the difference of both and take the average. The dates include a window that is sometimes over a day. I read this By @GrantSmith which is useful. Is there an easy way to do this without breaking up each segment (day, hour, minute, second) then…
-
Case statement with multiple conditions
currently I have this query and I have 16 exclusion reasons. Is there a more efficient way to write this beastmode? CASE WHEN exclusion reason 1 IS NULL THEN CASE WHEN exclusion reason 2 IS NULL THEN CASE WHEN exclusion reason 3 IS NULL THEN CASE WHEN exclusion reason 4 IS NULL THEN CASE WHEN exclusion reason 5 IS NULL…
-
Flag for duplicate record
I have two datasets, same structure/columns, 1 large dataset with all records and another large dataset with suspected duplicate customers removed. I want to join these together and create a flag for the suspected duplicate records. I tried to add a formula tile to each dataset, Duplicate = Yes ( for dataset filtered dupes…
-
Average Time between Orders
Stuck doing this in Magic ETL: Trying to calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer. Customers have multiple orders so I can't use Max(date)
-
Sandbox
How do you add a dataset in sandbox? Not finding much documentation
-
Reg Ex difficulty
I am attempting to pull out a string using Reg ex. I want to get the card name, so text after the --- card_name: and between measurable_id. I am using .*measurable_id) but it is giving me an error message?
-
Dataset View
Can you schedule a view to run daily?
-
Row Count growth change
Trying to get the %change in overall Row Count DoD looking at today vs. yesterday. This is what I have but is not successful. Any suggestions?: (SUM(CASE WHEN DAY(`Snapshot Date`) = CURDATE() THEN `Row Count` ELSE 0 END) - SUM(CASE WHEN DAY(`Snapshot Date`) = CURDATE(-1) THEN `Row Count` ELSE 0 END)) / SUM(CASE WHEN…
-
Daily Row count growth
What is the best way to show row count growth of a dataset day over day? Currently using DOMO Stats to show each datasets total row count I think I can create a beastmode for this, any suggestions here?
-
Snapshot Dataflow
I have a current dataflow that is recursive. I want to make sure the company records that are being snapshotted are. I am planning to use a field "Snapshot Date" which is the current date timestamp on the recursive. How would I go about assuring the companies are being snapshotted daily in the ETL? Would I need to match…
-
Snapshot Fields Accuracy
I'd like to create a new dataflow, preferably not MySQL, to show that the fields I intend to snapshot daily are actually being snapshotted. For more context, I have a list of companies with subscription updates that are currently being snapshotted, but I want to ensure that everyday, every company is included. Anyone have…
-
Month Snapshot
I'd like to build a recursive dataflow, but capture data by month. Any idea how this can be solved?
-
Can you use _Batch_Last_Run_ as the date for a recursive dataflow?
Have a dataset that doesn't have a timestamp that is being imported via connector. Can I use the batch_last_run as the date to create the recursive dataflow?
