Welcome to the webcast about TRAX, the Tool for RSR and ADR
Contents
1. CHEX and then re save those CHEX Excel tabs as separate CSV files Identify KERELE Become familiar Prepare input structure of and create with data elements files input files file If you do find issues with your data you can fix them using one of the following methods 1 Correct them in your source data and re generate your files 2 Fix them in the CSV files 3 Fix them in CHEX and then re save those CHEX Excel tabs as separate CSV files 25 Creating CSV Files from CHEX CHEX has some columns not in the CSV templates that allow us to run validations on the data If you create your CSV files directly from the CHEX spreadsheet delete these extra columns Has LastViralLoa LastViralLoa 7 SexAtBirthic Record Load data and create file If you use that last approach make sure to delete some of CHEX s extra columns that we created to help with the validations TRAX won t accept a file with extra columns 26 Install TRAX https performance hrsa gov hab trax publish htm Leidos TRAX Name TRAX Version 2 0 0 2 Publisher Leidos The following prerequisites are required e Windows Installer 3 1 e Microsoft NET Framework 4 Client Profile x86 and x64 If these components are already installed you can launch the application now Otherwise click the button below to install the prerequisites and run the application Load data m Identify A Become familiar Prepare input st2. Data TA caiglobal org Be prepared to share your desktop with us through webinar software we ll send you a link We may connect you with the developers at Leidos if necessary 32
3. Become familiar with the ADR data elements Identify the structure of the required input files Prepare your input files Use CHEX to check data quality Install TRAX Load data into TRAX and create the client level data XML file Now let s go over the steps to using TRAX Become familiar with the ADR data elements Identify the structure of the required input files Prepare your input files Use CHEX to check data quality Install TRAX Load data into TRAX and create the client level data XML file Sa a a ADR Instruction Manual Detailed description of ESEE ADR data elements https careacttarget org library adr instruction manual Version 4 November 1 2014 on Load data Become familiar iss and create with data elements file The first step is to become familiar with the ADR data elements You can do this by carefully reading two resources posted on TARGET The ADR instruction manual has a detailed description of all ADR data elements ADR Data Dictionary v3 1 Required values for HRSA Lifecycle Information Technology Support Services each data element pp ADAP Data Report ADR Client Data Dictionary v3 1 https careacttarget org 40 30 ibrary adap data m report adr download ARSAN package m Identify Korle R ELE Become familiar Prepare input structure of and create with data elements files input files file The data dictionary has the required
4. Tool for RSR and ADR XML Generation TRAX ADAP Data Report ADR HIV AIDS Bureau March 25 2015 abt SCA AN Messow Welcome to the webcast about TRAX the Tool for RSR and ADR XML Generation My name is Michael Costa and am part of the DART Team one of the Technical Assistance providers responsible for the RSR and the ADR am joined by my colleague from DART Ellie Coombs This webcast will help you decide if TRAX formally known as Rx REX is for you as well as give you detailed instructions on how to use TRAX for the ADR Now l Il turn this over to our presenter Ellie Outline Overview of TRAX Steps for Using TRAX TRAX is a free tool that you can download from the TARGET center website It take files with your client level data as input and converts those data to the correctly structured XML file l Il start the presentation with an overview of TRAX including why we developed it and differences between TRAX and Rx REX Then we ll go into more detail about each step Throughout this discussion l Il be pulling up resources and demonstrating key activities This webinar is very detailed but don t worry There is a detailed user manual with the same information that comes along with the TRAX download package The webinar recording and slides will also be posted on TARGET This webinar just focuses on TRAX for the ADR If you are here for the RSR you should go to the TARGET Center website and review t
5. ate of birth and gender ID You can leave the URN and eUCI columns blank 18 ADAP Creates the URN e Populate the ClientURN field with the pre created URNs Leave other fields blank If you already create the 11 digit UCI and you just want TRAX to encrypt it for you just populate column D and leave the other columns blank Note that GenderID is an ADR data element so you need to input that regardless of your approach 19 ADAP Creates the eUCI e Populate the ClientUCI field with the pre created eUClIs Leave the ClientURN name and DOB fields If you already create the eUCI then you can populate the ClientUci column and leave the other columns blank Once again you need the GenderlID regardless because it is an ADR data element 20 Viral Load For undetectable viral load counts o Report the lower test limit for the viral load count o If you don t know the threshold report 0 for clients with undetectable status astCd4Date LastCd4Co LastViralLoadDate LastVirall Client s viral load 3 14 2014 438 3 14 2014 320 is undetectable 10 15 2014 113456 10 15 2014 48 Threshold of 42 Another data element that we have received questions about relates to viral load The ADR does not accept undetectable for the viral load value Therefore for your undetectable clients you should input the test value under which a client is considered undetectable In this example you see this clie
6. drClientRepor tMedicalinsurance 2 9 2015 9 48 AM El adrClientReportMedication 12 29 2014 9 07 AM El AdrCientReportNhpiSubgroup 12 23 2014 6 02 PM El AdrClientReportRace 12 23 2014 6 02 PM El AdrClientReportRecertificationDate 12 24 2014 6 52 AM El AdrinsuranceAssistanceReceived 12 23 2014 6 05 PM Become familiar structure of and create with data elements input files file Each of the subsequent files represent one data element that allows for multiple responses Prepare Input Files Extract data from your system e Use the CSV files as templates to correctly structure data o Column order does not matter but column headers should be identical o Replace your values with ADR required values o Files must have identical names se Identify Kore Me ELE Become familiar Prepare input structure of and create with data elements files input files file Once you have downloaded the support package and identified where each of the ADR data elements are you must extract client level data from your system or from multiple systems and get it into the format of the 10 CSV files Some grantees are able to write programming code that allows them to directly pull data into the CSV file format Others may need to extract data in one format and then do some manual formatting Regardless this is probably the hardest step to using TRAX Here are some things to keep in mind while you structure your data Although the c
7. e for Yes This is being fixed so that TRAX will be aligned with the data dictionary but in the meantime use True and False 23 CHEX Checking Data Quality An Excel spreadsheet pre loaded with drop down menus and conditional formatting Copy and paste your data into each tab Validate data to find invalid values Review color coded cells to identify other validation issues oe Identify Kor Re ELE Become familiar Prepare input structure of and create with data elements files input files file The next step is to use a tool called CHEX It is an Excel spreadsheet pre loaded with drop down menus and conditional formatting to help you check the quality of your data It is also located in the support package There is a tab for each of the 10 CSV files Once you have your CSV files populated you can copy and paste them into the 10 tabs You Validate data to find invalid values and review color coded cells to identify other validation issues There is a tab for every CSV file The first check is on valid values You can see the drop down menu here that tells you which values are acceptable To make sure your values match those go to Data Data Validations and Circle Invalid values Through conditional formatting CHEX also color codes cells if there are issues 24 Strategies for Fixing Data Correct issues in your source data and re generate your files Fix issues in the CSV files Fix issues in
8. hat is required for the ADR you ll need to start creating the input files which consist of 10 CSV files If you don t know what CSV is don t worry It is essentially the same as Excel and you can easily create a CSV file from an Excel file using Save As To know how to structure your data go to the TARGET Center website input your email address and download the support package The support package is a zip file with the TRAX user manual and templates for the 10 input files TRAX CSV Files i 15 x X Search ADRCientReportData 9 i tas AdrClientReport E AdrClientReportAsianSubgroup Date modified 3 18 2015 12 57PM 12 23 2014 5 57 PM 3 23 2015 3 31 PM Type Microsoft Excel Com Microsoft Excel Com Microsoft Excel Com Size E AdrClientReportDisenrolimentReason eas AdrClientReportHispanicSubgroup tasi AdrClientReportMedicalinsurance EL AdrClientReportMedication El AdrClientRepor tNhpiSubgroup EL AdrClientReportRace El AdrClientReportRecertificationDate E AdrInsuranceAssistanceReceived 12 23 2014 5 59 PM Microsoft Excel Com 2 9 2015 9 48 AM Microsoft Excel Com 12 29 2014 9 07 AM Microsoft Excel Com 12 23 2014 6 02 PM Microsoft Excel Com 12 23 20146 02PM Microsoft Excel Com 12 24 2014 6 52AM Microsoft Excel Com 12 23 2014 6 05 PM Microsoft Excel Com Identify PEGE EIE Become familiar structure of and create with data elements input files file Here
9. he webinar we did in October specifically for the RSR Why Use TRAX You don t use an ADR Ready System to capture ADR data You don t have the resources to create your own XML export About half of ADAPs used Rx REX last year to create the ADR client level data file TRAX is a good tool if you don t already input your data into an ADR Ready System such as CAREWare and you don t have the resources or desire to create the XML export function yourself It s probably easier to get your data into the input files required for TRAX than to create the XML file TRAX versus Rx REX Tool needed an overhaul due to 2014 changes e Technological improvements o Does not rely on MS Access No 32 64 bit issue o Self updating o Same tool for the RSR and ADR want to compare TRAX and Rx REX Although TRAX was mainly developed because the ADR was changing so much Leidos decided to build a better mouse trap For one we ve had some issues with Access Rx REX was not compatible with 64 bit operating systems Therefore TRAX removes Access out of the equation entirely In addition TRAX is self updating After you install TRAX and then open it again if there have been any updates you will be prompted to install the latest version This means we don t have to find you and email you every time we make a change Finally TRAX also works with the RSR You use the same tool for the different reports Steps to Using TRAX
10. is a view of the inside of the folder you ll download from the TARGET Center website with the 10 CSV files You ll create a folder that looks exactly like this but the files will be populated with your data TRAX CSV Files 15 x X Search ADRClientReportData 9 he El AdrClientReport E AdrClientReportAsianSubgroup E AdrClientReportDisenrolimentReason EL AdrClientRepor tHispanicSubgroup 12 23 2014 5 59 PM El AdrClientRepor tMedicalinsurance 2 9 2015 9 48 AM El AdrClientReportMedication 12 29 2014 9 07 AM EL AdrClientRepor tNhpiSubgroup 12 23 2014 6 02 PM m EL AdrClientReportRace 12 23 20146 02PM_ Microsoft Excel Com EL AdrClientReportRecertificationDate 12 24 20146 52AM_ Microsoft Excel Com EL AdrInsuranceAssistanceReceived 12 23 20146 05PM_ Microsoft Excel Com Identify PEGE EIE Become familiar structure of and create with data elements input files file The first file contains the bulk of the data elements in the ADR It has all the data elements with just one response per client like poverty level and new enrollment flag You ll have just one row per client in this file 11 TRAX CSV Files 15 x X Search ADRChentReportData BJ 00 Date modified Type Size 3 18 2015 12 57 PM Microsoft Excel Com El AdrClientReportAsianSubgroup 12 23 2014 5 57 PM El AdrClientRepor tDisenrollmentReason 3 23 2015 3 31PM El AdrClientReportHispanicSubgroup 12 23 2014 5 59 PM El A
11. nt has an undetectable viral load The test has a threshold of 48 If you don t know the threshold report 0 Note that there is no longer an IsDetectable field in TRAX 21 Report a row for each unique combination of client D code and dispense date e Client has three rows o Two D codes o One D code with two dispense dates G 1 Clientid Medicatio MedicationStartDat Medicatio 2 21165 d00034 9 10 2014 30 21165 d00034 12 10 2014 30 21165 d03858 12 10 2014 30 also want to note that in the medications file you report a row for every unique combination of client D code and dispense data In this example the client has three rows One D code has one dispense data and another D code has two dispense dates Note that TRAX only accepts D codes not NDCs so you must map these codes prior to loading data into TRAX 22 Reporting Flags Three flags yes no question o New client o Medication services o Insurance services Data dictionary 0 No and 1 Yes e TRAX accepts False No and True Yes have one final comment on tricky data elements There are three flags in the ADR meaning that they require a yes or no response They are 1 Is the client new 2 Did the client receive medication services And 3 Did the client receive insurance services According to the data dictionary ADAPs should report 0 for No and 1 for Yes However this version of TRAX 2 0 0 2 only accepts False for No and Tru
12. ntReport has multiple data elements related to the eUCI The ones you populate depend on whether you already create the eUCI or whether you want TRAX to create it for you 15 What Composes the eUCI Name Joe S itith Birthdate Gender code 1 Male JES112 20681 CCD741F7984DD5DA59955CC74B3B9EA6DF5203FEM As a reminder the eUCI is created from the first and third letters of the first name first and third letters of the last name date of birth and a code for gender This creates the 11 digit URN or unencrypted UCI That URN is then encrypted into a 40 digit string A 41st digit lets HAB know if any other client within the ADAP s file has the same eUCI It should be U for unique or A B C etc to distinguish clients 16 Identifier Fields in the ADRClientReport File 11 digit UCI digit eUCI F 1 Clientid FirstName LastName ClientUrn ClientUci fthnicityld ClientDateofBirth GenderiD _ LO o do OJ S f ee Data elements that compose the eUCI Now let s take a look at those columns related to the eUCI in the ADR Client Report file These are the ones that represent the data elements that create the eUCI This contains the 11 digit unencrypted eUCI And this contains the 41 digit eUCI 17 ADAP Does Not Create the eUCI e Populate o Name o Date of birth o Gender e Leave the ClientUCI and ClientURN fields blank If you do not already create the eUCI you need to populate the first and last name full d
13. olumn order does not matter the columns headers do They must be identical to the ones in the CSV files You also must replace your values with the ADR required values Also the file names must be identical to the ones in the template 13 ClientID is the Primary Key Unique sequential or internal number no letters First column in every file ClientID must be the same for a given client across files i a a ae ee File1 Client Report 11 2 1983 4 12 1998 12 18 2001 Mala i 014 Same person File 2 Health Insurance Now let s talk about a couple of specific data elements The records of clients within each file are linked by the Client ID It is the first column in every file It can be a sequential number or internal number like a medical record number They can t have letters They can only have numbers This number must be unique for every client You must make sure that the same client has the same ID in each table Let s look at an example Carmelo has an ID of 1 Through this same ID in the health insurance file we can see that he has health insurance status of 11 and 16 14 TRAX and the eUCI There are multiple identifier fields that relate to the eUCI in the ADRClientReport tab The ones you populate depend on whether you already create the eUCI or would like TRAX to do it for you Another important data element want to go over is the eUCI That first CSV file which is the ADRClie
14. ructure of and create with data elements i files input files file 27 Avoid Common Mistakes Close files prior to upload Include all files even if you don t have data just the header row Capitalization matters 29 Missing Data In the ADR Client Report file leave cell blank In the other files exclude clients entirely Mtld DisenrollmentRea 30 Data Security Keep the populated Excel and CSV files secure o Store on a secure system o Only use secure transfer methods e Lots of resources on TARGET o Data Security Refresher https careacttarget org library data security refresher o Data Academy https careacttarget org content ensuring security your clients E2 80 99 data Now want to discuss data security The client XML does not contain personally identifying information such as name or full date of birth However most likely your Excel spreadsheet or CSV file will So want to emphasize the importance of keeping these data secure Store your CSV files on secure systems such as behind firewalls Also only use secure transfer mechanisms to transfer the files If you would like more information about data security just go to TARGET and doa search on data security There are lots of archived webcasts and resources there 31 Resources and Questions e Accessing TRAX https careacttarget org library trax adr e Questions Start with the DART Team
15. values for each data element Create an ADR Crosswalk Where do these data elements live in your system s o What is the field name o What are the response options How do I need to transform my data Your Data RacelD ADR Data RacelD White Black Hawaiian PI 2 Asian 3 4 5 Native American AK Native Become familiar Identity Prepare input Load data structure of and create with data elements files input files file Next you ll need to create a crosswalk that maps the data elements in the ADR to the data elements in your data management system Find out where these data elements live and how they are coded This will help you assess how you ll need to format your data to correctly report the ADR In this example with the RacelD variable your own database might categorize clients as white black etc However the ADR requires this variable be categorized as numbers 1 through 5 Using this crosswalk it will be easier to see and track what data transformations are necessary as white becomes 1 black becomes 2 and so on Download Support Materials Instruction manual ARSE Sens gt Templates for input nn files 10 CSV files Se aaa https careacttarget org library trax adr dd a Commen i Identify A KERELE Become familiar Prepare input structure of and create with data elements gt 3 files input files file Now that you have a good idea of w
Download Pdf Manuals
Related Search