Filtering Dates to Get the Last Access Date

CCCDomo

Hello All,

 

I have created a card with the following fields (see Table 1) and trying to edit it using the Beast mode. I am basically looking for help to filter rows the courses last access by a user with the number of days (Today’s Date - DayAccessed). Below is a user “John Doe” who accessed two courses on three different days.

 

Table 1

FirstName	LastName	CourseName	DayAccessed
John	Doe	Fall 2020 CIS 101	2020-11-21 00:00:0000
John	Doe	Fall 2020 CIS 101	2020-11-20 00:00:0000
John	Doe	Fall 2020 CIS 101	2020-11-19 00:00:0000
John	Doe	Fall 2020 BIO 222	2020-11-19 00:00:0000
John	Doe	Fall 2020 BIO 222	2020-11-17 00:00:0000
John	Doe	Fall 2020 BIO 222	2020-11-16 00:00:0000

 

 

Result will look like this table 2 (below) showing:

1. Only the row that includes the last day the user accessed each course
2. A new additional column (DaysAccessed) containing the number of days since last accessed
3. Plus change the DayAccessed field from “2020-11-21 00:00:0000” to “2020-11-21”

Table 2

FirstName	LastName	CourseName	DayAccessed	DaysAccessed
John	Doe	Fall 2020 CIS 101	2020-11-21	4
John	Doe	Fall 2020 BIO 222	2020-11-19	6

 

Thanks in advance.

Syed

 

MarkSnodgrass

Here's a screenshot of what it would look like in analyzer to backup my earlier post.

 

RACERX

Hey Syed,

 

RE: DateDiff Column -- Add a calculated field for DATEDIFF(CURRENT_DATE - `DayAccessed`) - it will give you the number of days between "today" and the value in the `DayAccessed` column.  You can then add this calculated field to your table.

 

RE: Date Formatting -- I'd personally recommend handling this at the ETL level so you don't have to constantly carry it around in your BeastModes. You would do this via CAST/CONVERT/SET COLUMN TYPE depending on your ETL vehicle. I'm making this recommendation presuming your "timestamp" values are meaningless. If you must handle it at a BeastMode level, then you can use DATE or DATE_FORMAT to convert to your desired output. 

MarkSnodgrass

You can show the most recent day accessed by clicking on the pencil icon for the Days Acccessed column in the column list and choose MAX. You can also format the date to just show the date in the same edit area by clicking on Format and choose Date Shorthand. 

To get the difference between the first day accessed and the last day accessed by a person and class, create a beast mode that contains this formula:

DATEDIFF(MAX(`DayAccessed`),MIN(`DayAccessed`))

 

You can then drag this field into your column list and it should produce the results you are looking for. 

GrantSmith

Hey @CCCDomo 

 

Within an ETL you'd need to do a Group By tile to group based on the First, Last and Course names. Then for your resulting column select the Maximum aggregate. That will get you the last time it was accessed. You can then take that data output and pipe that into a Date Operations tile to calculate the number of days since last accessed. Just select "Difference Between Dates" for the operation and "Days" for your unit of measurement. Then choose "Value" and then select "current date" from the date selector for the first value and your "DayAccessed" for the second value. This will then get you the data you need.

CCCDomo

Thanks for your super quick reply RACERX!

 

I am getting the following error when I try to add the filed using DATEDIFF(CURRENT_DATE - `DayAccessed`) although the Best mode validate without any problem.

 

"An issue has occurred during processing. We are unable to complete the request at this time."

 

I do not think it is happening because field DayAccessed is kind of timestamp format compared to the output I get from the "CURRENT_DATE" which is slightly different because I also tested using the following with the same error:

 DATEDIFF(CURRENT_DATE - 1) and DATEDIFF(CURRENT_DATE -  CURRENT_DATE)

They both validate fine too.

MarkSnodgrass

Here's a screenshot of what it would look like in analyzer to backup my earlier post.

 

GrantSmith

@MarkSnodgrass 's solution would work as well and likely be simpler. I have seen DATEDIFF not work correctly sometimes so I prefer the UNIX_TIMESTAMP method where it converts dates to a number in seconds and then doing some simple math:

 

ROUND((
  UNIX_TIMESTAMP(CURRENT_DATE) - UNIX_TIMESTAMP(MAX(`DaysAccessed`))
/
  60 * 60 * 24)
- 0.5, 0)

 

Using -0.5 and the ROUND function it's truncating the number of days to be whole numbers that have passed.

CCCDomo

Thanks MarkSnodgrass!

 

I go the first two working including the last date of access filtered and the date format changed.

To get the number of days last access I made minor changes to the code you provided as below and it is working too.

DATEDIFF(CURRENT_DATE(),MAX(`DayAccessed`))