Hi,
I'm seeking advice on displaying data in a single row of an Amazon Quicksight table. I need to show the start and end date of a user interaction based on specific actions and results, excluding redundant information.
For example, consider this simplified data:
| Date | Action | Result | Names |
|---|
| 2023-12-26 15:58:10 | PAYMENT_CHANNEL | Transferred to an agent | Juan Pérez |
| 2023-12-26 15:58:32 | PAYMENT_CHANNEL | Conversation initiated | Juan Pérez |
| 2023-12-26 15:58:57 | PAYMENT_CHANNEL | Email sent | Juan Pérez |
| 2023-12-26 15:59:07 | PAYMENT_CHANNEL | Informative audio | Juan Pérez |
I've tried using firstValue and lastValue functions to calculate the interaction period and dateDiff to get the duration, but this resulted in duplicated rows with null values, as shown below:
| Date | Action | Result | Names | Start date | End date | Duration (seconds) |
|---|
| 2023-12-26 15:58:10 | PAYMENT_CHANNEL | null | Juan Pérez | 2023-12-26 15:58:10 | 2023-12-26 15:59:07 | 00:00:57 |
| 2023-12-26 15:58:32 | PAYMENT_CHANNEL | null | Juan Pérez | 2023-12-26 15:58:10 | 2023-12-26 15:59:07 | 00:00:57 |
| 2023-12-26 15:58:57 | PAYMENT_CHANNEL | null | Juan Pérez | 2023-12-26 15:58:10 | 2023-12-26 15:59:07 | 00:00:57 |
| 2023-12-26 15:59:07 | PAYMENT_CHANNEL | Informative audio | Juan Pérez | 2023-12-26 15:58:10 | 2023-12-26 15:59:07 | 00:00:57 |
This also happens because I have to add the attributes of conversationid and date in order to use first and last value functions. I know I can hide those columns but the problem is the duplicated rows.
The result I am expecting is to display the data as it follows:
| Action | Result | Names | Start date | End date | Duration |
|---|
| PAYMENT_CHANNEL | Informative audio | Juan Pérez | 2023-12-26 15:58:10 | 2023-12-26 15:59:07 | 00:00:57 |
How can I achieve this and eliminate the unwanted duplicated rows with null values and the different dates which cause the rows to duplicate?
I appreciate any insights or suggestions on the best approach for this scenario.
AWS OFICIALAtualizada há 2 anos
This might work in the ETL process, where I can manipulate the raw data, this is the last thing I have in mind but I wonder if there is any solution to apply in AWS Quicksight, which doesn't let me run SQL queries.
Ok. In filter, Try Null options? You could use filter and exclude NULL value if it shows in filter.