pstrauss Member

Ahhh… this appears to be the right way to do it. COUNT(DISTINCT(`Main Ticket ID`)) / SUM(COUNT(DISTINCT(`Main Ticket ID`))) OVER ()

FYI, this SEEMS to work but I wanted to validate that it is the right way to do it: COUNT(DISTINCT(`Main Ticket ID`))/ COUNT(COUNT(DISTINCT(`Main Ticket ID`))) OVER () UPDATED: That doesn't work. Do I have to do windowing in my ETL to display a percent of total on a State chart? Seems kind of silly since you can do it on…

@GrantSmith Is it possible to use %_PERCENT_OF_TOTAL on a state map visualization? Otherwise how can I show percentages of total on that instead of counts?

Got it, thanks!

@colemenwilson - if I do the group by ID tile, won't I lose the additional rows for that same ID? The ID is in the same table as the individual interaction rows

Thanks @colemenwilson I'm going to try that approach and will post back if I can't figure it out.

@MarkSnodgrass thanks for the suggestions. I'll give this a shot and see if I can make it work.

@MarkSnodgrass thanks for the quick reply. Can you think of a way to use this approach to query for a date range? So not just all open tickets on a specific date, but open tickets during a date range (i.e. 3/1/2023 to 3/31/2023)

@AKnowles we looked into the writeback connectors option and unfortunately, it's cost prohibitive to add this feature to our account for just this simple use case.

I've got a similar challenge here: Assuming I'll need to create a table with 30-minute increments of time and figure out a way to join to that in an ETL to generate a row for each 30-minute block of time?

@MarkSnodgrass so I tried converting the values to numbers for the hours but that doesn't work because I'd dealing with AM and PM - so “100” AM and “100” PM get grouped together in the chart. I'm still perplexed as to why SOME hours sort properly and others don't when using a text sort. I've also tried converting the field…

Thanks so much for the comprehensive suggestion @bobbyragsdale - I'll give this a try in our environment!

Managed to make it work. Thanks @GrantSmith

So I'm facing a similar issue where I have a bunch of multiselect values where multiple values are stored in a single comma-separated column. I have some logic in my ETL that does a pivot/unpivot in order to be able to report on them, but it's caused a MASSIVE row explosion with about 10 columns I have to do this with. Can…

Thanks @GrantSmith that's a good idea.

On a related note, what's the most reliable way to filter out rows with specific values in them but not null rows? For example, I want to remove rows where column_1="A" OR column_1="B" but not where column 1 is NULL? I originally tried two filters with an ALL setting where column_1<>"A" or column_1<>"B" but it seems to…

Thanks @GrantSmith and @RobSomers that's very helpful!

Thanks, @MichelleH that's exactly what I needed!

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

@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…

The Domo Integration suite can do this, but it's a very expensive add-on, so we decided against using it.

So doing the multiple column splits and multiple unpivots is causing the ETL to run extremely slowly - I'm looking for any alternative solutions to handling a bunch of columns that took like this: "value1,value2,value3,value4,value5" And splitting them and putting them into separate rows for a single new column. Any other…

So this is still driving me nuts - I used this solution for a single column, but I have about 10 columns where I have to do the same thing - do a lookup from an external table to change the values to match those. Is there any simpler way to do this across multiple columns?

This is what I ended up doing... Maybe it's not ideal... Since that CSV list never has more than 10 values in it, I joined the data from the external lookup table, then split the column into 10 columns, then unpivoted the 10 columns so each one is now a row. Not particularly elegant, but it seems to do the trick.

Does anyone know if the Zendesk Connector can pull in the Display Names for custom fields rather than the tag? So far I think my only solution to getting the proper Display Names for custom fields in my reports is to do a JSON API call to pull in the Field Details and Values separately and then join to that data in Magic…

I've used the pivot tile for some other ETL work, but never the unpivot ones. I'll take a look and see if that might help.

Thanks @MarkSnodgrass - that was what I thought I might have to do. It's just that there are like 20 columns where this needs to happen, and new columns get added to the primary data set which need the same sort of mapping on a regular basis. Was hoping for a more scalable solution, but this might be the only approach