BLT Stages

Abstract 2019-2020: Clinical pathology data retrieval and visualization with Qlikview

Background and objectives

Data retrieval in the clinical lab was only accessible to specific persons due to need for extensive knowledge of the Glims statistics module with its MISPL coding language. With this project our objective was to improve the accessibility and visualization of clinical pathology data to clinical biologists and other healthcare actors that are active in the clinical biology lab.

Methods

The first step was retrieving data from the Glims databases in a structured way with Python coding language. This was achieved by only querying the needed tables and fields as to remove redundant data, splitting the data into the 3 main fields: routine results, microbiology results and quality control results with each main field having multiple sub fields. The queried sub field data is written into multiple .csv files by date (DD-MM-YYYY) into a structured folder layout (field -> year -> sub fields). To prevent unnecessary long queries but still retaining up-to-date data, queries are dynamically programmed to retrieve data from a specific period for each field (e.g. last 2 weeks) every day. The python program is being run as a virtual environment by a windows server task scheduler that starts a .bat file. While the python script is running every step is logged and protected by a try and except clause, if an error would occur a mail will be sent that contains a summary of the error(s). To visualize the structured data created by the Python script we use the already present Qlikview program. User input was gathered at different intervals during the design process in order to cover all clinical and data retrieval needs.

Results

For the routine data and microbiology data fields a yearly Qlikview file is made due to increasing loading time equal to data size, QC data is present in one Qlikview file. The .csv files are loaded into the corresponding Qlikview files where the data can be further manipulated if needed without altering the original data. In the Qlikview work file multiple selector fields are added for easy filtering of the data (corresponding to the users question criteria) and the filtered data can be shown in multiple premade tables and charts of which the user can select the best fitting one(s) for their question. If this would not be sufficient, the user can extract the filtered raw data to a .csv file for further analysis with Excel, R, SPSS or other statistical programs.

Conclusion

The new data retrieval; cleaning and presentation greatly improves clinical data accessibility and visualization. Flexibility is greatly improved by eliminating the need for custom  MISPL coding and by allowing real-time data filtering & visualization. The user now has immediate control for filtering data & drilldown by selecting the needed fields and values and desired visualization in Qlikview with a broad array of tables and graphs covering most questions. In addition the tables and charts can be exported for secondary use in reports or statistical programs.