Timestamp filter for column with null values

Question & Answer
,
1

Hi there, I am looking to have a filter and a visual with a column which contains a timestamp column. This timestamp column has null values in it. When I attempt When I attempt to create the visual/filter, I get the error “Amazon Quicksight can’t parse the data because it contains invalid dates or invalid numbers. Make sure your data contains only supported number and date formats”.
Is there a way I can work around this to have a timestamp column which contains null values, and can be filtered?

2

HI @purpledonkey17 - Can you please share the sample input and expected output and what is your data source? Ideally you can filter date fields and select only not null columns.

Please provide the details so that community can guide you the right approach.

Regards - Sanjeeb

3

Hi @Sanjeeb2022 , thanks for a quick response.

Data source is an athena table, with a column which I have generated from the following calculated field:
parseDate(ifelse({last login date}= 'NaT', '',{last login date}), 'yyyy-MM-dd hh:mm:ss'). The source column, last login date looks like this
last login date
2023-06-24 13:05:43
2023-06-22 13:05:43
NaT
2023-05-13 13:05:43
Expected Output is a table, with this column looking like this:
last login date
2023-06-24 13:05:43
2023-06-22 13:05:43

2023-05-13 13:05:43

We would then have a filter for this column, allowing the end user to select a timestamp range of last login values to look at.

Best,
Magnus

4

Hi @purpledonkey17 - Can we use custom sql and put a where condition and filter the data at dataset level? This way you may not require to create a calculated field also.

Please give a try.

Regards - Sanjeeb

5

Hi Sanjeeb, unfortunately in this case we are looking for a filter which is on the analysis. This is because we would like the end user of this dashboard to be able to filter by this column.

Best, Magnus

6

Hi @purpledonkey17 - Looks like QuickSight is not able to parse the date when the field is having both date and string, can you create another field in Athena which will parse the “NaT” records to Null and then you can use this field and parse the date at QuickSight.

Hi @David_Wong @Max @Naveed - Any expert advise on this?

Regards - Sanjeeb

1 Like
7

Try to change the empty string to null instead:

parseDate(ifelse({last login date}= ‘NaT’, null,{last login date}), ‘yyyy-MM-dd hh:mm:ss’)

1 Like
8

Hi David, thanks for your support.
Unfortunately, this gives the same error as before when added to a visual.

9

Hi @purpledonkey17,

I think there’s probably an issue with your last login date field because I tested parseDate with null and it works fine. You can check it by replacing {last login date} in your calculated field by a known date which is in the correct format. There are probably some values of {last login date} in your data that are not in the correct format.

2 Likes
10

Hi David,
Apologies - I think I’ve miscommunicated the issue somewhere.
That calculated field formula works fine. The issue is in the analysis, when I try to use said calculated field in a table. This returns a “Amazon Quicksight can’t parse the data because it contains invalid dates or invalid numbers. Make sure your data contains only supported number and date formats” error.

11

Can you create another dataset using the same source but with only a few rows of data for troubleshooting? Using the smaller dataset, if you can add the calculated field to a table visual without any errors, then there’s something wrong with some of the dates in your bigger dataset.

2 Likes
12

Thanks David, this worked! I had a bad entry in the dataset. This is now solved

13

Thanks @David_Wong for sharing the details…