-
Count No of Records Based on unique ID
Trying to count number of unique records for each customer id in beast mode. Currently using: COUNT(DISTINCT CASE WHEN `unique ID`=`unique ID` THEN COUNT(DISTINCT `Records`) END) Any help? Keep getting error message.
-
Select Top 1 MySql
Does any one know what am doing wrong here? I am trying to convert a sql statement to Domo Mysql but I am getting an error Original sql: SELECT ta.Item , ta.Warehouse , ta.ShipDate , tb.ActivityDate FROM copy_of_cuts_w_forecast ta JOIN RunningCases tb ON tb.Item = ta.Item AND tb.Warehouse = ta.Warehouse AND tb.ActivityDate…
-
Magic ETL 1.0 rename Output Dataset
Hi, I am working on a dataflow to take an input dataset and write an output CSV file based on another vendor naming convention. Is there a way that I can automatically name the output dataset in this format? Example: Output dataset = CU_CSV_FILE_YYYYMMDD.csv so for today it would be CU_CSV_FILE_20210423.csv. thx!
-
Convert DATETIME (UTC) to specific timezone
Hello, Domosapiens! I have an interesting quandry. I have a data flow (using MagicETL - should I be using something else?) that I am trying to build and convert a date that my MySQL data connector is bringing over. The datetime column comes from an orders table and is called posted_date and it brings the data over as UTC…
-
Aggregating Rows
I have a dataset that breaks everything everything out by rows. any time there is a rental, or vacate it adds a value in to a new row with a siteID for a store. the data refreshes on a daily basis, so it site 3196 has 4 rentals in a day it would repeat site 3196 on 4 different rows with a value of 1 in the rental column…
-
Improve Dataflow Efficiency
Hello, Domosapiens, I currently have a data flow that is taking 14 min or greater to run. The design is as such: MySQL connector that first runs once to pull in all historical data (7.8M rows). Ex full historical pull query: select * from table; change dataset settings to merge method by changing the query to select * from…
-
Impact of deletion ETL, input datasets
Hello, Let me send my question. Several Input datasets, ETL, one output dataset, and the cards are linked in sequence. Those input datasets are no longer updated, so the output dataset will not change anymore. As a result, ETL and input datasets are no longer needed. If I delete the ETL and those input datasets, will the…
-
Schedule report to send out to email of my co-workers.
Hi, When I schedule a report to send out to my co-work from DOMO Card, there is no option for me to choose the file to send out as Excel. It sends out as a CSV file. Is there a way to change it from CSV to Excel file? Any insight will be greatly appreciated! Thank you very much!
-
Invalid query check your dataset schema.
I have run an ETL within Magic ETL and got error "Invalid query check your dataset schema" while viewing data output. Can anyone be able to assist in solving this error.
-
Rows Gone Missing in ETL
I created an ETL. The final step -- where it leads to the output suddenly causes ~20K rows to go missing. Any insights? Thanks!
-
Filtering is not supported Error
Hi, I built a Radio Button selector card as a filter for 2 other cards. The 2 other cards are based off of 2 different datasets. The filter allows to filter for degree vs. no degree and the other 2 cards : one has information about average life expectation and the other average earnings. I created the degree field in all 3…
-
Adding an "Activity_Date" to a nightly refresh dataset.
I just set up a connection for a dataset that doesn't have any fixed 'date' column. The only date column it will have will be the batch_last_run date. On a nightly basis this dataset will append and start to build history. How can I add a new date column in a format such as 'Jul 1, 2021', so that the new data and old data…
-
Filter by aggregate work around?
Hi there, I'm aware that you cannot filter by an aggregate (even if created in breast mode). I'm wondering how I could create the aggregate in magic ETL or SQL. My knowledge on both is quite limited. My raw data looks something like this: Staff Course Jon course 1 Jon course 2 Suzie course 1 Jon course 3 I would like it to…
-
How to Export filtered dataset?
I have a 1 million row dataset in Domo. I want to filter the data, and only export that data in a CSV. When I filter the data and export, Domo exports the whole 1 million rows, which does not run on my Excel. Anyone know how I can download the filtered dataset>
-
Formula to search text
Hi domo fam! I am currently working on two ELT formulas: the first I am trying to search a string field column named Note for a specific text. However the way the text is stored in the row it is “Bankruptcy Record-then a break line-Dismissal Customer confirmed bankruptcy on 01-01-2021” I want to search for all notations…
-
Summing unique values based on a separate column
Having issue trying to sum values that have a one to many relationship without over-counting the value. I have marketing events that I'm trying to attribute opportunity created from and get the overall ROI while maintaining drill down functionality within the dataset itself. This is leading to marketing events that…
-
Extract Items from a list
I have a mongodb data set that has a list of items nested within a field. I am trying to check if certain items are contained within that list, preferably in SQL. Is there a good way to do this in domo, such as a SPLIT or explode() function?
-
How to dynamically add "missing" data
Hi everyone, I have a list of equipment that I want to attach to a date column for everyday of the year Example: The problem is that I have the list of equipment, I just don't know how to add the date column in domo for everyday this past month. I also want to continue creating date data for everyday that passes, so once…
-
Error when creating a case statement for a data flow column
What am I doing incorrect? It gives me an error while using the case statement.
-
Week to Week Sales Comparison
I'm looking for a way to use beast mode to calculate last week's sales and compare them to this week's sales. The syntax I used for the monthly calculation worked but I'm failing to make the weekly one work. Last Month: SUM((CASE WHEN (month(curdate()) = 1) THEN (CASE WHEN ((year(`filter_date`) = (year(curdate()) - 1)) AND…
-
Rating Level to Currency
Hello, I'm having trouble creating a beastmode that would convert a rating level into a currency. I would like Rating Level 1 to be worth $2,500 and for each increase rating level the amount would increase $2,500. So for example, Rating Level 4 would be $10,000. Does anyone have any suggestions on making this in a…
-
1st of the month with Current Month Filter
I'm trying to create a date filter that on the 1st of the month it shows previous month otherwise it's MTD. However, it must also allow for the date range to remain dynamic. The below code allows me to show MTD and on the 1st it will show last month. However, I lose all date range functionality. Does anyone have any…
-
MagicETL2 Filter issue?
I have a filter in my dataflow: id NOT EQUAL value This filter removes all rows that have 'value', but it also seems to remove rows that have null in the 'id' field. Has anyone seen similar behavior in the Filter Rows ETL?
-
Duplicates : counting them, not removing them?
Whats the best way to count duplicates in a table using ETL and or Beast mode on a card? Lets say I have a table of data that shows me how many times each class uses a specific tool so I end up with a table with a list of classes who have used the tool. And, each class can have multiple entries showing each time they…
-
Show Top 10 in Chart (Rank in Beast Mode)
Hi, I am trying to build a multiline chart based on the following Dimension: Date (x-axis), Search query (line series) Metrics: impressions and position The multiline chart should show the position (y-axis) by date for only the top 10 search queries based on the total impressions during a the selected time period. I cannot…
-
What is wrong with my Pivot - Uncollapse Columns? Showing Null
Hey I'm trying to pivot a column, but the new columns are all null Here is the original data This is how I've set up the pivot info This is the output I get: Originally not every Customer_ID had ever Level(1/2/3) I thought this was the cause of the nulls but I've fixed it so every Customer_ID has all levels and still the…
-
Show all dates between start and end date columns on calendar card
I have three columns; 'user name', 'start date' and 'end date' representing time off from work. I'm wanting to use a calendar card and show the days where someone will be off work. Currently, I can only use one date column which I've chosen 'start date', however, it only represents the first day someone is off work. Is…
-
Joining or Appending Data Help
I have a dataset (dataset 1) with individual rows as a specific job (with an identifier key) with details including year and country of operations. I have created cards to show the total number of jobs per year. I have another dataset (dataset 2) which shows historical revenue by Year and Country but it is not specific to…
-
Unique Values Pulling Through twice
I've used an excel upload method and I'm tidying up the dataset using Magic ETL. On one of my columns there is a value labelled "Test". For some reason it is pulling through as two unique values when it should only be one. I have tried to use 'replace text' but whatever I replace it with it pulls through again as two…
-
How can we use similar function as Vlookup??
I would like to offer a volume discount price for each company A, B, C and calculate total biil. There are 2 datasets like the below. When we sell 250 of Products to Company A in 2021 Feb (Dataset B), I would like to apply Price 4 USD on the price list for Company A (Dataset A). Then the total bill will be 1000 USD. If…