Related product I-Share

UB Stat 5: Unfilled Requests, Import Directions

Overview

The UB Stat 5: Unfilled Requests report lists the titles that your patrons requested to borrow through I-Share, where the patron's request was unfulfilled; this report does not include charges your patron did on-site at another library if they did not place a call slip request.

This report does not include any requests where the patron canceled their own request. This report is formatted for importing into Access or Excel for further investigation.

This webpage includes the steps to import the ubstat_5_xxx_mmyy_for_import.txt file so that the data can be further analyzed.

Important notes about the report data (please read):

  • The FINAL_REQUEST library is the library where the call slip expired, aka, surpassed its eligibility for promotion.
    • In many cases, the request was promoted between multiple I-Share libraries before reaching expiration at the FINAL_REQUEST library.
    • Therefore, the STATUS_DESC reflects why the call slip request ended at the final library, but does not represent other possible call slip statuses that the request may have had previously at other libraries before promotion.
  • The data in the STATUS_DESC column:
    • For all intents and purposes, both Not Filled and Expired can be translated to mean "Unfulfilled" or "Not Filled".
    • Not Filled: The FINAL_REQUEST library marked the request as Not Filled in Voyager Call Slip; the request was unable to be promoted to another I-Share library so it ended here.
    • Expired: The FINAL_REQUEST library neither filled nor marked the request as Not Filled in Voyager Call Slip; the request was unable to be promoted to another I-Share library so it ended here.
  • Most titles have only one NORMAL_HEADING_2450, however, it is possible for a title to have more than one, especially if the title is in another language. 
    • Titles with multiple NORMAL_HEADING_2450 entries may make it appear that the title had additional patron requests.
    • If a title has multiple rows, and all of the data for the title is identical (same DATE_REQUESTED, same FINAL_REQUEST, same FINAL_REQUEST BIB_ID), check to see if there are multiple, varying NORMAL_HEADING_2450 entries. 
    • Highlight or delete your preferred extra NORMAL_HEADING_2450 row before sharing the statistics or sharing the report with collection developers.
  • Each time a call slip is promoted from one library to the next, the DATE_REQUESTED is updated by Voyager to be the date of promotion into the library receiving the promoted request.
  • Remember that promoted requests are passed from one I-Share library to the next at the title level; promoted requests do not consider the SysAdmin matrix entries, and may be for materials that have a non-requestable item type at the owning library.

The ubstat_5_xxx_mmyy_for_import.txt file contains these fields:

  • PATRON_LIB
  • FINAL REQUEST
  • DATE_REQUESTED
  • STATUS_DESC
  • PATRON_GROUP
  • CALL_NO_TYPE
  • DISPLAY_CALL_NO
  • NORMALIZED_CALL_NO
  • FINAL REQUEST BIB_ID
  • TITLE
  • AUTHOR
  • PUBLISHER
  • PUBLISHER_DATE
  • NETWORK_NUMBER
  • NORMAL_HEADING_2450 (beginning with the April 2017 report)
  • ISBN or ISSN

UB Stat 5 report generation:

  • This report is in a text file (.txt) format. This report will automatically be run on the 15th of each month with the file uploaded to your library's CARLI FTP account.
    • Like all files in your FTP account, they will automatically be deleted 60 days after their date of creation.
  • There is no annual version of this report. However, CARLI staff can run it for any period that you need.
    • File a work request to request this.
    • Please note that if requesting historical datasets, titles that have been deleted by the FINAL_REQUEST library will no longer be included in the results. Due to record deletion, the numbers from this report will not exactly match the "Unfilled" counts generated by UB Stat 4.
    • UB Stat 5 itself cannot be run in Access because it requires that data from all of the I-Share databases be combined.

updated 4/21/2017

  1. Save the ubstat_5_xxx_mmyy_for_import.txt file file to your computer from your library's CARLI FTP account.
  2. Open Microsoft Excel.
  3. In the File Menu, choose Open, and then browse for the file on your computer. Select the file to open in Excel.
  4. The Text Import Wizard will open.
  5. In the Text Import Wizard, step 1:
    1. Set the file type as "Delimited".
    2. Check the checkbox for "My data has headers".
    3. Click Next.
  6. In the Text Import Wizard, step 2:
    1. Set the delimiter to "Other" and enter the pipe symbol "|" (the pipe is Shift + backslash "\").
    2. Click Next.
  7. In the Text Import Wizard, step 3:
    1. update the formatting for all of the fields to "Text".
      • If desired, you can chose to set the DATE_REQUESTED to the Date formatting, YMD style.
    2. Click Finish.
  8. Your data is now imported.
  9. Save the file as an Excel Workbook.
  10. Optional edits: In the View menu, choose Freeze Panes, then "Freeze Top Row".
  1. Save the ubstat_5_xxx_mmyy_for_import.txt file file to your computer from your library's CARLI FTP account.
  2. Open your Voyager Microsoft Access Database.
  3. Note: If you have imported a dataset previously, you may want to rename the table named "UBStat5Import" that is already in your database so that it is not overwritten by the steps below. Right click on the existing UBStat5Import table, and rename it with the date it contains. For example, "UBStat5Import1605."
  4. Click on the External Data tab; under Import & Link, select Text File.
  5. The "Get External Data" window will open. Browse for the file on your computer.
    Under "Specify how and where you want to store the data in the current database", choose: "Import the source data into a new table in the current database." Click OK.
  6. The Import Text Wizard will open.
  7. In the Import Text Wizard, step 1:
    1. Set the file type as "Delimited".
    2. Click Next.
  8. In the Import Text Wizard, step 2:
    1. Set the delimiter to "Other" and enter the pipe symbol "|" (the pipe is Shift + backslash "\").
    2. Check the checkbox for "First Row Contains Field Names".
    3. You may get a warning that says "The first row contains some data that can't be used for Access Field Names. In these cases, the wizard will automatically assign valid field names."
    4. Click Next.
  9. In the Import Text Wizard, step 3:
    1. Click on each column in order to highlight it; make sure every column in the data is set to "Short Text" (or "Text" in some versions of Access).
    2. The fields you will most likely need to change are DATE_REQUESTED, CALL_NO_TYPE, and FINAL_REQUEST BIB_ID.
    3. Click Next.
  10. In the Import Text Wizard, step 4:
    1. Let Access add a primary key.
    2. Click Next.
  11. In the Import Text Wizard, setp 5:
    1. Set the "Import to Table" name to be: UBStat5Import
      You must use this table name to run the associated shared SQL, discussed in the next section on Analyzing the Data: Access Queries and VuFind.
    2. Click Finish.

Microsoft Access Queries

Notes that apply to reducing duplication in all queries:

  • Most titles have only one NORMAL_HEADING_2450, however, it is possible for a title to have more than one, especially if the title is in another language. 
    • Titles with multiple NORMAL_HEADING_2450 entries may make it appear that the title had additional patron requests.
    • If a title has multiple rows (both in the original report or in the results of Microsoft Access shared SQL reports), and all of the data for the title is identical (same DATE_REQUESTED, same FINAL_REQUEST, same FINAL_REQUEST BIB_ID), check to see if there are multiple, varying NORMAL_HEADING_2450 entries. 
      • If the NORMAL_HEADING_2450 entries are identical, the multiple rows likely represent multiple unique patron requests.
      • If the NORMAL_HEADING_2450 entries are different, the multiple rows represent the same patron request.
    • Highlight or delete your preferred extra NORMAL_HEADING_2450 row before sharing the statistics or sharing the report with collection developers.
  • The UBStat5 Conspectus Query set
    This query set from the CARLI Shared SQL compares the titles listed in the UBStat5 report (ubstat_5_xxx_mmyy_for_import.txt imported into Access following the directions above) with the Conspectus Categories to help collection development by subject area.
    • This query set may contain multiple rows for a single request, if the requested item's call number is categorized under multiple conspectus categories.
    • This query set will contain a request even if it does not match with a Conspectus Category. In those cases, the DIVISION and CATEGORY fields will be blank.
    • When importing the data into Microsoft Access, the directions above suggest that you allow Microsoft Access to add a primary key; as a result, each single patron request in the table is tagged with a unique "ID" number in the ID column.
    • Steps to use the primary key to denote the duplication for your collection developers:
      1. Export (or copy/paste) the query results from Microsoft Access into Microsoft Excel.
      2. Highlight the entire "ID" column (column O).
      3. On the Excel Home tab, select Conditional Formatting> Highlight Cell Rules> Duplicate Values. Select the formatting you'd like to apply to the duplicated IDs and click OK.
      4. Each ID that is highlighted as a duplicate value is already included elsewhere in your spreadsheet, most likely under a different conspectus category.
      5. You can further highlight these rows to let your collection developers know which IDs represent a single request, but were included in the results under multiple conspectus categories.
         
  • UBStat5- Normalized Title Match- Titles In Collection
  • UBStat5- Normalized Title Match- Titles Not In Collection
    • This query set returns a list of results where the normalized titles (NORMAL_HEADING_2450) requested by your patrons did not match the the normalized titles your library's database.
    • This query's results have a high chance of containing multiple rows for a single request.
      • Multiple rows will happen when:
        • the requested item has more than one NORMAL_HEADING_2450 value.
    • Since there was not a match in your library's database, the results of this report display the record information for the requested title. Using the OCLC Number (NETWORK_NUMBER), ISBN or ISSN, plus Title, Author, Publisher, and PubDate fields can help you identify a title, should your library wish to order it for your collection.
       
  • UBStat5- OCLC Number Match- Titles In Collection
    • This query set returns a list of results where the OCLC numbers (NETWORK_NUMBER) in your library's database match the OCLC number for titles requested by your patrons.
    • This query's results have a high chance of containing multiple rows for a single patron request.
      • Multiple rows will happen when:
        • the requested item has more than one NORMAL_HEADING_2450 value.
        • your library owns multiple versions of the same title on different bibs.
    • Matching on the OCLC number is a more specific match than NORMAL_HEADING_2450; as such, the query results require less interpretation than the "UBStat6- Normalized Title Match- Titles In Collection" results.
    • Since OCLC numbers can change over time as duplicate records are merged and deleted in OCLC's global WorldCat catalog, a match could be missed by this query if either the OCLC number in the REQUEST LIB BIB_ID or your own library's record was not updated to the newest OCLC number.
    • Either your library, or the REQUEST LIB, may occasionally store non-OCLC numbers in the NETWORK_NUMBER field. Unless both libraries have the same value entered, a match will not be made.
       
  • UBStat5- OCLC Number Match- Titles Not In Collection
    • This query set returns a list of results where the OCLC Numbers (NETWORK_NUMBER) for titles requested by your patrons did not match the the OCLC Numbers your library's database.
    • This query's results have a high chance of containing multiple rows for a single patron request.
      • Multiple rows will happen when:
        • the requested item has more than one NORMAL_HEADING_2450 value.
    • Since OCLC numbers can change over time as duplicate records are merged and deleted in OCLC's global WorldCat catalog, a match could be missed by this query if either the OCLC number in the REQUEST LIB BIB_ID or your own library's record was not updated to the newest OCLC number.
    • Since there was not a match in your library's database, the results of this report display the record information for the requested title. Using the OCLC Number (NETWORK_NUMBER), ISBN or ISSN, plus Title, Author, Publisher, and PubDate fields can help you identify a title, should your library wish to order it for your collection.
    • Either your library, or the REQUEST LIB, may occasionally store non-OCLC numbers in the NETWORK_NUMBER field. Unless both libraries have the same value entered, a match will not be made.

Reviewing Data in VuFind

If you would like to know more information about the requested item than is present in the ubstat_5_xxx_mmyy_for_import.txt file itself, you can use the data from two columns to locate the Bib record in VuFind.

  • The FINAL_REQUEST column contains the three letter code of the library where the request expired (became no longer eligible for further promotion).
  • The FINAL_REQUEST BIB_ID column contains the BIB_ID number at that library.

Plug this data into the base VuFind URL:
http://vufind.carli.illinois.edu/vf-xxx/Record/xxx_#
where xxx is the FINAL_REQUEST library's three letter code, and # is the FINAL_REQUEST BIB_ID.

Example:

FINAL_REQUEST FINAL_REQUEST BIB_ID
npu 201801
VuFind URL: http://vufind.carli.illinois.edu/vf-npu/Record/npu_201801

Return to top