-
Automate Exports from Cards via SFTP
We have several use cases where we need to export report data that appears on cards in CSV or pipe-delimited formats and then deliver them to clients via SFTP or an AWS S3 bucket. It would be amazing if there were a way to do the following: 1) Schedule exports of the data that is typically emailed as a CSV and send it to…
-
Any Way to Report on Column Usage in Cards
We are trying to filter out unnecessary and unused columns from our datasets and it would be really useful to be able to create a DomoStats or Governance report that shows which columns are used on cards and which are not used at all. Is there any way to do this?
-
Calculating Percent of Total, and Getting Correct Totals
I have this table where I'm using count(distinct()) to get counts of various attributes, but when I add a "Percent of total" column to each one, the percentages add up to more than 100%. In addition, my totals at the bottom of the counts are incorrect. I've attached a screenshot of the issue along with an example of the…
-
Provide a Method to Block Domo Access from Specific Countries
We have a policy that states that no users will access our data from outside of the U.S. but I don't see a way to do anything but IP whitelisting. It would be great if there was a way to permit/block access based on geo-IP lookups for specific countries.
-
S3 Advanced Connector Not Loading JSON Files
I'm trying to automatically load all files ending in ".json" from an S3 bucket using the S3 Advanced Connector. However, no data is loading. I set the File Name field to ".json" and the File Name Match Type to "File name contains the specified string." I have no problem reading individual files from this bucket without a…
-
S3 Connector Files in Subdirectories
We're working on a pipeline that moves custom JSON log data from Cloudwatch to an S3 bucket using Kinesis Data Streams and Firehose (as explained here). This process will copy new log exports into a nested directory structure that represents the year/month/day/hour, for example: /2024/02/20/01/file1.json…
-
Calculating Differences based on the Value of a Column
I'm trying to solve a problem where I want to compare the average of two columns based on the value of another column. A simplified version of my data table looks something like this: Score A | Score B | Score C | Survey Type 10 | 20 |19 | pre 14 | 26 |23 | post 7 | 6 |8 | pre 10 | 10 |12 | post I want to calculate the…
-
How to Calculate Averages Across Two Different Columns
I'm trying to get the average duration of our phone calls and chats. For rows that have a Unique Call ID, we want to use Call Duration, for rows that have a Unique Chat ID, we want to use Chat Duration. There may be multiple records with the same Chat ID or Call ID, so I've been using FIXED BY to ensure we're using unique…
-
Fill empty columns based on values in other columns
I'm trying to figure out a way to do the following in an ETL. Given data that looks like this, I want to propagate the email address from the first row with a specific ID to all other rows that match that ID. I need to do this in order to attribute all interactions of all sorts to a specific user's email address: Input…
-
Query Based on a Value Between Two Date Columns
I've got a data set that includes job tickets with create_date and last_update columns. If the date range for a card is set to include a date in between the create_date and last_update, the ticket should be considered "open" at that point in time, and any tickets outside of the date range should be considered "closed" at…
-
Exporting Report to SFTP Server
There's a particular table-style report that we need to publish to an SFTP server on a weekly basis. Currently, we have to email the XLSX or CSV file and then manually upload it to the SFTP server. Is there any way to use Domo to schedule and submit files to an SFTP on a regular basis? I see that there is an SFTP writeback…
-
Filtering Records Based on Aggregate Criteria
I've been trying to do the following but can't figure out the best way to do it. I have a table that looks basically like this: user_id|ticket_id|inquiry_type I want to filter out rows for a specific user_id when ALL of the rows for that user_id have a value of "false_inquiry" in the inquiry_type field. But if the user has…
-
Creating a Heat Map Based on Start and End Times
I have a data source that includes one row each for scheduled activities, with a column with the start date/time and end date/time (i.e. 2022-09-06T09:00:00 and 2022-09-06T13:00:00). What I need to produce is a heat map showing blocked out times in hour or half hour increments on a heat map, with the days of the week as…
-
Heatmap Time Sorting Issue
I recently created a weekday and half-hour heatmap using Beast Mode functions to break timestamps down to half-hour increments. But for some reason, the chart is sometimes sorting the half hours into the wrong order. Notice how 3:30 PM appears before 3:00 PM in the chart below, as does 830 PM before 8 PM and 930 PM before…
-
Any Way to Use Regular Expressions to Change Case of Specific Words
I've got a requirement to change the appearance of articles and prepositions in words in a column to be displayed as lowercase after running a Title Case on the rest of the phrase. Right now, I have an extensive list of text replacements in Magic ETL to do this, but I was wondering if there might be a way to do this with a…
-
Heatmap in 30 Minute Intervals
I've created a heatmap based on the timestamp of records with the day of week on the Category 1 axis and the hour of day on the Category 2 axis. However, the business is asking for a version that breaks things down into 30 minute intervals. I'm currently using the HOUR function on the timestamp to extract the hour, but I'm…
-
Magic ETL: How to REMOVE Rows Meeting a Two-Column Criteria?
As part of my ETL, I'm trying to REMOVE rows that meet the following criteria: column 1 is NOT NULL AND column 2 is NULL So if column 1 has any value in it AND column 2 is empty, then remove those rows from the data set. I was trying to figure out how to create a Filter Formula for this, but it seems to be designed for…
-
Printing Cards that are Longer than One Page
I know this has been brought up many times in the past and it appears that each relevant thread was closed without resolution. We need a way to be able to print or output a PDF or PPT of a card without it being cut off if it doesn't fit entirely on one page. Currently we have several reports that won't fit on a single page…
-
How to Deal with Multiple Date Fields
I have a perplexing issue I'm trying to solve. I have data coming from several different data sources (all parts of Zendesk), each of which each represent a different kind of user interaction. For example: Email, Chat, Phone Calls These are all related to a master "Tickets" table, so they can be joined based on a Ticket…
-
Sending Card Data via SFTP
Is there any way to send the output of a card (XLSX or CSV format) to an SFTP server? We are looking for a way to schedule weekly updates of a report and have it automatically sent to an SFTP directory. I saw that there is an SFTP writeback connector available as part of the Domo Integration Studio, but that appears to be…
-
Multiple Date Columns
So I have a data set that looks basically like this: And I want to produce a card that counts the number of chats, emails, web, and text tickets by date, but also the number of calls by date. Then I need to sum up all interaction types by date. So for the above example, I would want this as the output: Can anyone provide…
-
Looking up values from secondary data source but for multiple columns in primary data source
So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL: -I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource"…
-
Replacing Values in Multiple Columns Based on a Lookup Table
So here's a challenge I can't seem to figure out how to solve in Magic ETL. I have a primary data source that has values written out as "tags" like "hc_res" and a secondary data source that maps those tables to pretty display names like "Healthcare Resources," which is what I need to display in reports. Normally, I'd just…
-
Combining Disparate Data Sources on a Card
I know it's not possible to create a card that pulls in data from multiple databases unless you combine them with a dataflow / ETL. But I have a scenario where that approach doesn't make sense as the data is VERY different in structure. Basically, one database has detailed individual end-user records which I'm totaling…
-
Looking up values from secondary data source for multiple columns
So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL: -I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource"…
