How can I display a single row with start and end date in Amazon Quicksight?

Question

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.

Answer

Simply eliminate NULL value. Use 'Result IS NOT NULL'  or length(Result) != 0 in where clause.

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

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

How can I display a single row with start and end date in Amazon Quicksight?

Relevant content