Clean up the “Published” field with Regular Expressions & Evaluate Functions

Use Case

• Report for weeding - include the publication date as a data point
• Just adding the “Published” field results in an unsortable column that includes the copyright symbol, square and angle brackets, periods, multiple years and text (such as “reprint”)

​

Goal

• Remove extraneous characters or words from the “Published” column
• Show one date
• Column is sortable

​

​

Remove non-digit characters

• Use a regular expression and “find and replace” to replace non-digit characters
• The database function that will do this for you is REGEXP_REPLACE
• To use many specialized database functions in Analytics, you have to enclose it in an EVALUATE function
• EVALUATE functions let you do some operations that aren’t supported by the Oracle Analytics Server, but that would be possible in the underlying database itself
• EVALUATE can be a very helpful tool

​

​

Remove non-digit characters

EVALUATE('REGEXP_REPLACE(%1,%2,%3)',"Bibliographic Details"."Publication Date", '\D','')

• Change your “Published” column formula to the above
• REGEXP_REPLACE has 6 possible arguments. For each one you will include, you place %n in parenthesis (highlighted in yellow above)
• Then specify the values that should go in your placeholders. At least one of these will be an Analytics column. In my example, we have…
• Analytics column (in blue),
• the regular expression (in green),
• and the replacement string (in red)

Show only the 4 leftmost numbers

SUBSTRING(EVALUATE('REGEXP_REPLACE(%1,%2,%3)',"Bibliographic Details"."Publication Date", '\D','')FROM 0 FOR 4)

• You’ll change your “Published” column formula once again to the above
• This time, you’re nesting your REGEXP_REPLACE function (which isn’t changing) in a SUBSTRING function that is going to return only the left 4 characters.
• For the SUBSTRING function, you specify a starting point (0 for the first character) and a number of characters to traverse (4 places in this case, for characters 0, 1, 2, and 3)

​

​

2022

0

1

2

3

EVALUATE('GREATEST(%1,%2)',SUBSTRING(evaluate('REGEXP_REPLACE( %1,%2,%3)',"Bibliographic Details"."Publication Date", '\D','')FROM 0 FOR 4),EVALUATE ('NVL(%1,%2)',EVALUATE('REGEXP_SUBSTR(%1,%2)',"Physical Item Details"."Description", '^(20|19)\d{2}'),0))

Sources/Resources

• Huge thanks to the Harvard Library Analytics documentation and this ExLibris blog post for explaining how to use the evaluate and regular expression functions within Analytics
• Regex101.com is helpful for testing regular expressions
• Database functions - Oracle SQL reference

​

Using Bins to Modify Date Output

Use Cases

• The way fiscal years have been labeled in Alma has changed over the years
• Sometimes, we want to see data by certain date ranges that don’t line up with consistent patterns or ranges understood by Alma (e.g., checkouts by term)
• Example - seeing if a pilot of a virtual ArcGIS lab reduced the number of laptops borrowed

Goal

• Create consistent, user-friendly date labels on reports without modifying the exported data (i.e., in Excel)

​

​

​

Bins

• Apply if…then…else logic (if the value is x, then do y…else do z)
• Uses a CASE statement
• Access in Analytics
• Click on gear cog on column
• Choose “Edit formula”
• Select the “Bins” tab

​

Bins

• In the Bins tab, click on “Add Bin” button
• Set the filter on the column appropriately for your needs (in my example, Loan Fiscal Year = 06/30/2021 - 06/29/2022)
• Give your bin a name
• This is what shows on your report
• If you have an “else” situation, check the box that says “create a bin for all other values” and specify what the “catch-all” or leftover bin would be

​

Before:

After:

Example #2 - Checkouts by term

• This use of bins labels a date range with the term and year shorthand

Example #2 - Checkouts by term

Before:

After: