How to Deal with Multiple Date Fields

pstrauss · 2022-08-15T21:35:39+00:00

There was an error rendering this rich post.

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 ID. A single ticket can have multiple Emails, Chats, Phone Calls, etc. Each table (chats, calls, emails) has its own date and time stamp.

When I Left Outer join each of these tables to the tickets table, I end up with a dataset that ends up with all of the dates in it, and I can't determine the actual interaction date - which is what I need for filtering on cards and dashboards.

I tried a Beast Mode calculation that says if there's a Chat ID in the row, use the Chat Date; if there's a Call ID in the row, use the Call Date, etc., but that doesn't work, since each row has ALL of the dates after the joins are complete.

I've been beating my head against the wall on this for a week now. Does anyone have ideas on how I might be able to determine the specific interaction date?

Find more posts tagged with

Date

Join

Magic ETL

Beast Mode

Accepted answers

All comments

RobSomers

@pstrauss If you just have single charts for each interaction (Emails, Chats, Phone Calls, etc.) then you can just graph by the interaction specific data. If you want everything to function off of a single date (all Emails, Chats, and Phone Calls for Jun 2022), then you'll have to stack your data. Instead of joining, you would append or union the tables with an additional column for each table that identifies what that table is. Then your chat date, call date, etc. all fall in a single date column, and your data would be organized like this:

Book1.xlsx

You'll then need to create beast modes to count each type of interaction:

SUM(case when 'Type' = 'Email' then 1 else 0 end)

pstrauss

@RobSomers thanks for the quick response and suggestion. We already have separate charts per interaction type, but the business is looking for totals across all interactions by date. Hence the requirement.

If I'm not joining the data and instead appending it, how will the ETL process handle it when each table has many different columns? Will it just leave those null when combining tables?

RobSomers

@pstrauss There a couple of options for handling different columns. Here's an article on appending and differing rows:

https://domohelp.domo.com/hc/en-us/articles/360044876194-Magic-ETL-Tiles-Combine-Data#:~:text=The%20Append%20Rows%20tile%20lets,data%20structure%20and%20data%20type.

The best way to guarantee that everything works together would be to select the specific columns from each table that you'll be using, and if certain tables don't have a specific column, just adding that column to the other tables with a null value before the append. Depending on the columns in each dataset, you can also experiment in the Append Rows tile with the options for what columns to use.

pstrauss

Got it. This is really helpful @RobSomers. I've already made some progress with a prototype using your suggested approach.

Screen Shot 2022-08-15 at 5.06.07 PM.png

Screen Shot 2022-08-15 at 5.15.45 PM.jpg