-
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…
-
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?
-
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"…
