Extracts (translating) in NHS e-Referral Service

The extracts in e-RS contain a wealth of useful information to support organisations in understanding referral volumes, workflows and actions. Information analysis knowledge is required to get the best out of these extracts, but in order to help those who have some analysis skills, this guidance aims to show how to decode one extract, using information from a different, supporting extract.

The instructions below show how to decode information from an extract to make the information more meaningful. This can be done by using a VLOOKUP function within Excel.

In this example we are using the EBSX05, generally known as the Directory of Services (DOS) extract, but the same principle would apply to any of the extracts in the system.

To understand the following points, review the ‘Extracts’ information within the information analyst section of the system Help (N3/HSCN connection required):

• the related extracts needed for translation
• what information each extract is made up of
• the data contained within the extract columns

The extracts used in the example we have used are produced monthly and can be accessed from within e-RS (by users with information analyst roles on their smartcards) or from the reports and extracts webpage. 

The information shows where the extracts can be found:

Extract name extract locations within e-RS and e-RS website  

• EBSX03 codified fields extract
• EBSX04 organisational
• EBSX05 service extract

Extract name extract locations e-RS website  

• EBSX02 UBRN action extract
• EBSX06 user extract
• EBSX08 role profile extract

This example shows how to decode the extract data labelled ‘Appointment Type’ Code, into meaningful information. This can be used to understand the type of appointments being supported by organisations.

Start by opening both of the extracts listed below for the month you want to analyse the data in. Make sure you open identical months, for example ebsx05May_2017 and ebsx03May_2017:

• EBSX05 - e-RS Provider Services – published and unpublished
• EBSX03 - codify fields extract

As prompted, you will need to save the zip file that opens and extract the files.

• in the EBSX05 apply a filter to the spreadsheet by going into the data tab and selecting Filter

• click on Column E (Provider_ORG_ID) to check your ODS code is displayed
• insert a column on the right hand side of Column C by right mouse clicking and selecting Insert from the menu

• name your column ‘Appt Type’, as this is where the appointment type will be shown
• place your cursor in row 2D
• click on the formulas tab and select ‘fx Insert Function’ or you can use the fx shortcut

• this will open the Insert Function dialogue box. If the VLOOKUP function is not immediately visible in the picklist then type in VLOOKUP in the Search for a Function search box and click on Go’

• when your VLOOKUP function is visible you can then click on it and select ‘OK’
• this will then generate a blank Function Arguments dialogue box

• lookup_value:  What data do you want translated? On the EBSX05, click on the first Row where your data appears (this is the data that you would like translated) and the value will be added to the field
• table_array: What is the source data that will be used in the translation? This is your EBSX03. First ensure you are clicked into the Table_array field in the Function Arguments box, then open the EBSX03 and click on the square between Column A and Row 1

• col_index_num: Which column in the data source contains your result? In this example it is Column C (the third column) of the EBSX03 so input the number 3 in the field

• range_lookup: This would either be TRUE or FALSE, type in FALSE

We have included an example of what your completed function agreement may look like:

• click ok
• you should now have your result in Column D on your EBSX05

• pull your data through to the end of the column by dragging down the small black square
• now hide column C

You are able to filter the results of column F (PROVIDER_ORG_ID) to find a specific ODS code if you need to