Multiple Date Columns

pstrauss

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 some tips on how I might achieve this with cards? Do I need to create a custom data set to somehow pivot this data into this format? How would you deal with the multiple date columns?

mhouston

I would probably do this in Magic ETL and do a combination of grouping and pivoting.

You could do a group by tile (on your input) on ticket date and ticket type and count ticket id - then use a pivot tile to create a column for each of your ticket types, which would give you the first half of your table. Then you could do a second group by tile (also from your input) on call date and count call id. Then use a join to join this to the pivot tile output by joining on date. This'll give you the output you're looking for but you lose the transactional detail of the ids.

Alternatively you could restructure your dataset so that every row is a single transaction, which may give more flexibility long term. You could do a select columns tile to select the ticket date, ticket id, ticket type columns, and use a constant tile to add a new constant (i.e. contact_type) and set it to 'Ticket'. Then another select columns to just select ticket id, call date and call id, and add the constant tile for contact_type = 'Call'. I'd rename the ticket_date and call_date columns to 'Date' so they'll be the same when you append. It would look like this:

Then you could do your aggregation in the card via beast modes (or possibly a pivot table), or could use a dataset view and use that to power a card.