Are you interested in analyzing trends of COVID19 infection?
I had a personal interest to analyze how the spread of COVID-19 behaves in various countries. I found a source of parsed data from https://www.worldometers.info/coronavirus/ at https://github.com/chrislopez24/corona-parser/and created an ALV-based output that can be exported to Excel file:
Should you be interested in making your own analysis, don’t hesitate to copy and modify the ABAP code as per your needs or simply download the attached Excel file which could be used for your own analysis, charts etc.
Disclaimer: Please note MTCD s.r.o. can’t be held responsible for incorrectness of the input or output data nor any damage caused by using the program. It is provided to you “as is” free of charge without any guarantee and support whatsoever.
*&---------------------------------------------------------------------*
*& Report ZMTCD_COVID19 COVID-19 HISTORICAL DATA AND TRENDS
*&---------------------------------------------------------------------*
*&
*& This report processes parsed historical data from worldometers.info
*& and displays it in the ALV. This way you can analyze various trends
*& including Case Fatality Rate for open and closed cases alike.
*&
*& Source files: https://github.com/chrislopez24/corona-parser/
*& The files are to be found in filepath: cases.csv -> History ->
*& choose date -> click "<>" button near to file -> click cases.csv ->
*& click "RAW" button -> save data as YYYYMMDDHHMMSS.csv
*&
*& The process needs to be repeated for all the dates. The files are
*& to be saved in one single folder. HHMMSS in the filename could be
*& replaced by zeroes provided you only have one file per date.
*&
*& The ouput is in ALV form. You can download the data in XLSX format
*& for further processing and charts creation.
*&
*& The "Infected Population %" field is only calculated for those
*& countries for which the value in lt_population is provided. If
*& you want to have the field calculated for specific Country/place
*& fill the value in the form prefill_population. The country name needs
*& to match the name in the CSV file(s).
*&
*& The report (in its original version) does not access your SAP DB data
*& nor does it write any data to your DB. It is however recommended to
*& save it as a local object (no transport) in the dev. system only.
*&
*& The code uses the new ABAP 7.4 syntax so it won't work on older SAP
*& ABAP releases. The source code is provided by MTCD s.r.o.
*&
*& MTCD s.r.o. does not guarantee the accuracy of the data provided by
*& worldometers.info and https://github.com/chrislopez24/corona-parser/
*& nor could it be held responsible for any damage caused by using the
*& report.
*&
*& MTCD s.r.o. is your reliable partner for SAP Consulting and Development.
*& You can find us on WWW.MTCD.EU
*&
*& The code is freely distributable and can be modified in any way without
*& prior consent of the license holder. Please note MTCD does not provide
*& ANY SUPPORT for this report whatsoever.
*&---------------------------------------------------------------------*
REPORT ZMTCD_COVID19.
PARAMETERS: p_dir TYPE string.
TYPES:
BEGIN OF ts_population,
country(20) TYPE c,
population TYPE i,
END OF ts_population,
BEGIN OF ts_cfr,
date TYPE sy-datum,
time TYPE sy-uzeit,
country(20) TYPE c,
total_cases TYPE i,
closed_cases TYPE i,
total_deaths TYPE i,
total_recovered TYPE i,
cfr_closed TYPE p LENGTH 3 DECIMALS 2,
cfr_all TYPE p LENGTH 3 DECIMALS 2,
infected_population TYPE p LENGTH 3 DECIMALS 2,
note TYPE text255,
END OF ts_cfr,
ts_file TYPE LINE OF filetable,
tt_cfr TYPE TABLE OF ts_cfr,
tt_population TYPE SORTED TABLE OF ts_population WITH UNIQUE KEY country.
DATA: gt_files TYPE filetable,
gt_population TYPE tt_population,
gt_cfr TYPE tt_cfr.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_dir.
CALL METHOD cl_gui_frontend_services=>directory_browse
CHANGING
selected_folder = p_dir
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE 'Error while selecting directory!' TYPE 'E'.
ENDIF.
START-OF-SELECTION.
PERFORM prefill_population CHANGING gt_population.
PERFORM get_files USING p_dir gt_files.
LOOP AT gt_files ASSIGNING FIELD-SYMBOL(<ls_file>).
PERFORM process_data USING <ls_file>-filename gt_population CHANGING gt_cfr.
ENDLOOP.
PERFORM display_alv USING gt_cfr.
FORM prefill_population CHANGING ct_population TYPE tt_population.
DATA: ls_population LIKE LINE OF ct_population,
lt_population LIKE TABLE OF ls_population.
APPEND VALUE #( country = 'Slovakia' population = 5459044 ) TO lt_population.
APPEND VALUE #( country = 'Germany' population = 83722922 ) TO lt_population.
APPEND VALUE #( country = 'Italy' population = 60481844 ) TO lt_population.
APPEND VALUE #( country = 'Czechia' population = 10704471 ) TO lt_population.
APPEND VALUE #( country = 'Sweden' population = 10084814 ) TO lt_population.
APPEND VALUE #( country = 'UK' population = 67804374 ) TO lt_population.
ct_population = lt_population.
ENDFORM.
FORM get_files USING iv_dir TYPE string et_files TYPE filetable.
DATA lv_count TYPE i.
CALL METHOD cl_gui_frontend_services=>directory_list_files
EXPORTING
directory = iv_dir
filter = '*.CSV'
files_only = 'X'
CHANGING
file_table = et_files
count = lv_count
EXCEPTIONS
cntl_error = 1
directory_list_files_failed = 2
wrong_parameter = 3
error_no_gui = 4
not_supported_by_gui = 5
OTHERS = 6.
IF sy-subrc IS NOT INITIAL.
MESSAGE 'Error reading directory' TYPE 'E'.
ENDIF.
LOOP AT et_files ASSIGNING FIELD-SYMBOL(<ls_file>).
<ls_file>-filename = iv_dir && '\' && <ls_file>-filename.
ENDLOOP.
ENDFORM.
FORM process_data USING iv_file TYPE ts_file-filename
it_population TYPE tt_population
CHANGING ct_cfr TYPE tt_cfr.
DATA: lt_data TYPE TABLE OF string,
lt_cfr TYPE TABLE OF ts_cfr,
ls_cfr TYPE ts_cfr.
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = CONV string( iv_file )
* filetype = 'ASC'
* has_field_separator = SPACE
* header_length = 0
* read_by_line = 'X'
* dat_mode = SPACE
* codepage = SPACE
* ignore_cerr = ABAP_TRUE
* replacement = '#'
* virus_scan_profile =
* IMPORTING
* filelength =
* header =
CHANGING
data_tab = lt_data
* isscanperformed = SPACE
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
not_supported_by_gui = 17
error_no_gui = 18
OTHERS = 19.
IF sy-subrc <> 0.
MESSAGE 'Error reading file' TYPE 'E'.
ENDIF.
DELETE lt_data INDEX 1. "delete header
DATA(lv_len) = strlen( iv_file ).
DATA(lv_len_date) = lv_len - 18.
DATA(lv_len_time) = lv_len - 10.
*Expected filenames: YYYYMMDDHHMMSS.csv
ls_cfr-date = iv_file+lv_len(8).
CLEAR ls_cfr-time.
LOOP AT lt_data ASSIGNING FIELD-SYMBOL(<lv_data>).
SPLIT <lv_data> AT ',' INTO TABLE DATA(lt_fields).
CHECK NOT lt_fields[ 1 ] CO '0123456789'. "only numbers in country name, skip such record
LOOP AT lt_fields ASSIGNING FIELD-SYMBOL(<lv_field>).
DATA(lv_tabix) = sy-tabix.
IF <lv_field> CS '"'. "need to concatenate several fields
DATA(lv_exit) = abap_false.
LOOP AT lt_fields ASSIGNING FIELD-SYMBOL(<lv_field2>).
CHECK sy-tabix > lv_tabix.
IF <lv_field2> CS '"'.
lv_exit = abap_true.
ENDIF.
<lv_field> = <lv_field> && <lv_field2>.
DELETE lt_fields INDEX sy-tabix.
IF lv_exit = abap_true.
EXIT.
ENDIF.
ENDLOOP.
ENDIF.
REPLACE ALL OCCURRENCES OF ',' IN <lv_field> WITH ''.
REPLACE ALL OCCURRENCES OF '"' IN <lv_field> WITH ''.
REPLACE ALL OCCURRENCES OF '+' IN <lv_field> WITH ''.
CONDENSE <lv_field>.
CASE lv_tabix.
WHEN 2 OR 4 OR 6.
IF NOT <lv_field> CO ',0123456789'.
DATA(lv_note) = 'Unpermitted value in field' && lv_tabix && '->' && <lv_field>.
<lv_field> = 0.
ENDIF.
ENDCASE.
ENDLOOP.
APPEND VALUE #( date = iv_file+lv_len_date(8)
time = iv_file+lv_len_time(6)
country = lt_fields[ 1 ]
total_cases = lt_fields[ 2 ]
closed_cases = lt_fields[ 4 ] + lt_fields[ 6 ] "closed cases = deaths + recovered
total_deaths = lt_fields[ 4 ]
total_recovered = lt_fields[ 6 ]
cfr_closed = lt_fields[ 4 ] / ( lt_fields[ 4 ] + lt_fields[ 6 ] ) * 100 "all deaths/closed cases
cfr_all = lt_fields[ 4 ] / lt_fields[ 2 ] * 100 "all deaths/total cases
infected_population = COND #( WHEN line_exists( it_population[ country = lt_fields[ 1 ] ] ) "do we maintain population data for given country? Skip if not
THEN lt_fields[ 2 ] / it_population[ country = lt_fields[ 1 ] ]-population * 100
ELSE 0 )
note = lv_note ) TO lt_cfr.
CLEAR lv_note.
ENDLOOP.
APPEND LINES OF lt_cfr TO ct_cfr.
ENDFORM.
FORM display_alv USING it_cfr TYPE tt_cfr.
DATA: lt_cfr TYPE TABLE OF ts_cfr,
lr_alv TYPE REF TO cl_salv_table,
lr_message TYPE REF TO cx_salv_msg,
lr_functions TYPE REF TO cl_salv_functions_list.
lt_cfr = it_cfr.
SORT lt_cfr BY country date time ASCENDING.
TRY.
cl_salv_table=>factory(
IMPORTING
r_salv_table = lr_alv
CHANGING
t_table = lt_cfr ).
CATCH cx_salv_msg INTO lr_message.
MESSAGE 'Error occured while creating ALV' TYPE 'E'.
ENDTRY.
lr_functions = lr_alv->get_functions( ).
lr_functions->set_all( abap_true ).
PERFORM set_col_names CHANGING lr_alv.
lr_alv->display( ).
ENDFORM.
FORM set_col_names CHANGING ir_alv TYPE REF TO cl_salv_table.
DATA:
lr_column TYPE REF TO cl_salv_column,
lr_columns TYPE REF TO cl_salv_columns_table.
TRY.
lr_columns = ir_alv->get_columns( ).
lr_column = lr_columns->get_column( 'COUNTRY' ).
lr_column->set_long_text( 'Country' ).
lr_column = lr_columns->get_column( 'TOTAL_CASES' ).
lr_column->set_long_text( 'Total Cases' ).
lr_column = lr_columns->get_column( 'CLOSED_CASES' ).
lr_column->set_long_text( 'Closed Cases' ).
lr_column = lr_columns->get_column( 'TOTAL_DEATHS' ).
lr_column->set_long_text( 'Total Deaths' ).
lr_column = lr_columns->get_column( 'TOTAL_RECOVERED' ).
lr_column->set_long_text( 'Total Recovered' ).
lr_column = lr_columns->get_column( 'CFR_CLOSED' ).
lr_column->set_long_text( 'Case Fatality Rate (closed cases only) %' ).
lr_column = lr_columns->get_column( 'CFR_ALL' ).
lr_column->set_long_text( 'Case Fatality Rate (all cases) %' ).
lr_column = lr_columns->get_column( 'INFECTED_POPULATION' ).
lr_column->set_long_text( 'Infected Population %' ).
CATCH cx_salv_not_found. "#EC NO_HANDLER
MESSAGE 'Error occured while setting field names' TYPE 'E'.
ENDTRY.
ENDFORM.