Display a single label from a list of several in database

Question & Answer
1

I’m working with a table to display individual tickets by Region and State. I’ve created custom calculation for the Region to group tickets by the folder ID of the Region. I’m having difficulty pulling the State information and grouping by State. The State data is saved by a “label_id” which gives a character text line e.g. “4019c1ed-0b66-48d5-80fc-ad04c2dae7” as well as “label_description” which is the displayed text of the label e.g. “Virginia”.

I’ve tried the string:
ifelse({label_description} = “Maryland”, “Maryland”, {label_description} = “Virginia”, “Virginia”, “Null”)

as well as

ifelse({labels_id}=“34e8559e-406e-4458-ad15-1174f03225”,“Maryland”,
ifelse({labels_id}=“bd875d48-f287-4e1c-a78f-94cb140fa3”,“Virginia”,
“Null”))

The issue with the above two statements is both label_description and label_id have several options for labels as we use labels for multiple features. When using the above lines all the other labels show as Null on the table which multiplies each ticket by the number of labels attached. I have a list of several states that need mapping from labels.

Example table of what I want below, orange is what I’m trying to accomplish.
image

I’d like to accomplish:

  1. Is there a way to filter out other labels besides the ones listed and if no label matches “Maryland” or “Virginia” etc… then it lists as “Null”?
  2. Being able to do this for multiple label types so I can have rows on the same chart for multiple labels.
2

Hi @Jon_D

Would it be possible for you to share an anonymised sample of the raw data and illustrate what you’re trying to achieve as the final output?

3

Sample Table of what I want with State being my question on how to make it.

+------------+-------------+------------+--------------+-------------------------------------+
|   Region   |    State    | aliases_id |    title     | extensions_tt_computedpendingreason |
+------------+-------------+------------+--------------+-------------------------------------+
| East Coast | Maryland    | V65623215  | Ticket Title | Step1                               |
|            |             | V8412325   | Ticket Title | Step1                               |
|            |             | V123326    | Ticket Title | Step3                               |
|            | Virginia    | V32615     | Ticket Title | Step4                               |
|            |             | V1326431   | Ticket Title | Step2                               |
| South      | Alabama     | V123123    | Ticket Title | Step1                               |
|            | Mississippi | V65348     | Ticket Title | Step1                               |
|            |             | V6563      | Ticket Title | Step3                               |
|            |             | V78991     | Ticket Title | Step4                               |
|            | Texas       | V63215     | Ticket Title | Step2                               |
+------------+-------------+------------+--------------+-------------------------------------+

Dataset 1 with Label_id and label_description

+-------------------------------+--------------------------------+---------------------------------+--------------------------+--------------+
|           ticket_id           |           labels_id            |        label_description        |        folder_id         | snapshot_day |
+-------------------------------+--------------------------------+---------------------------------+--------------------------+--------------+
| b85d97b0-0ef3-45f3-b11c-23764 |     6bba-a31a-4f0d-a7e6-8c5145 | Week 1 Sprint                   | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| 96b33f0d-8838-4dc7-843a-9c6bb |     6bba-a31a-4f0d-a7e6-8c5145 | Week 1 Sprint                   | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| 96b33f0d-8838-4dc7-843a-9c6bb |  6e8adf64-8eb5-4631-a38f-e36b6 | Virginia                        | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| 96b33f0d-8838-4dc7-843a-9c6bb | e3272c11-9740-4ca4-91f6-52521c | Network                         | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| 96b33f0d-8838-4dc7-843a-9c6bb |     6bba-a31a-4f0d-a7e6-8c5145 | Week 1 Sprint                   | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  |  6e8adf64-8eb5-4631-a38f-e36b6 | East Coast                      | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  | e3272c11-9740-4ca4-91f6-52521c | Network                         | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  |     6bba-a31a-4f0d-a7e6-8c5145 | [Network] AMER-East 2023 Sprint | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  |  102e89ae-036f-48be-a34b-692cb | [01] Survey Completed/Attached  | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  |  5f428402-9f35-4b0e-a3f0-c0b38 | [02] Repair Ticket Created      | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
| c0d985-f512-47a2-b307-1e5c49  |   82d86d32-09c2-4000-a9d5-1059 | Maryland                        | 4d2ef79a-a6df-a06f-235ba |      5/24/23 |
+-------------------------------+--------------------------------+---------------------------------+--------------------------+--------------+

Dataset 2 with extension tt

+------------+--------------+-------------------------------------+----------------------------+
| aliases_id |    title     | extensions_tt_computedpendingreason |     Assigonedfolder_id     |
+------------+--------------+-------------------------------------+----------------------------+
| V65623215  | Ticket Title | Step1                               | da44-2581-4860-9b32-c21353 |
| V8412325   | Ticket Title | Step1                               | da44-2581-4860-9b32-c21353 |
| V123326    | Ticket Title | Step3                               | da44-2581-4860-9b32-c21353 |
| V32615     | Ticket Title | Step4                               |  0091-40fb-4533-8b98-64281 |
| V1326431   | Ticket Title | Step2                               |  0091-40fb-4533-8b98-64281 |
| V123123    | Ticket Title | Step1                               | ad08-a4d8-4884-835c-5e4398 |
| V65348     | Ticket Title | Step1                               | ad08-a4d8-4884-835c-5e4398 |
| V6563      | Ticket Title | Step3                               | ad08-a4d8-4884-835c-5e4398 |
| V78991     | Ticket Title | Step4                               | ad08-a4d8-4884-835c-5e4398 |
| V63215     | Ticket Title | Step2                               | ad08-a4d8-4884-835c-5e4398 |
+------------+--------------+-------------------------------------+----------------------------+

What I’m trying to do is add a column between Region and aliases_id. This column will populate the state label from “label_Data” to group by ticket for that state. The issue I’m having is if I use “ifelse” any label attached to the ticket will show as NULL on the table. I want it to only show the State Label. On the “Label_data” set you can see ticket_id have multiple “label_descriptions” attached to them. I only want the column on the table to show the State label, if there is not a matching state label attached to the ticket ID (aliases_id) I want it to show as Null.

4

Hi @Jon_D Thanks for providing more info.

I am wondering if during your data prep, are you able to create a calculated field for state as you’re doing, apply a filter to exclude Null and do a left/right join with Dataset 2 to include all rows from Dataset 2 and the state calculated field from Dataset 1? This way your final dataset will have a state column which will either have a value populated or have Null if no state record existed in Dataset 1 for a given ticket_id

Assumptions made:

  1. Dataset 1 can have either 0 or 1 row with state value for a given ticket_id
  2. you’re already joining the two dataset together within QuickSight