Read a validation report into a normalised findings table

Reads a CDISC validation report (Pinnacle 21 style) from .xlsx or .csv and returns one tidy tibble whatever the report version, so downstream code never has to care whether the column was called Rule ID, RULE_ID or Check ID. Replaces the usual PROC IMPORT plus a hand-written RENAME block per report vintage.

read_findings(path, sheet = NULL)

Arguments

path

Path to a .csv, .xlsx or .xls validation report.

sheet

For Excel reports, the sheet to read. Defaults to the sheet named "Details" if one exists, otherwise the first sheet.

Value

A tibble of class p21_findings with at least the columns rule_id, severity, domain, variable, message, count and rule_class. Any columns of the source report that are not part of that canonical set are kept, with normalised names.

A real Pinnacle 21 Details sheet pairs its Variables column with its Values column position by position, and writes the placeholder VARIABLE where the finding is about a variable rather than about one of its values (Variables = "VARIABLE, LABEL", Values = "ANL01FL, Analysis Flag 01"). For those rows variable is taken from Values, so it holds the variable name a fix handler can act on rather than the literal string "VARIABLE". The original columns are kept untouched.

Examples

csv <- system.file("extdata", "findings.csv", package = "p21bridge")
f <- read_findings(csv)
f
#> 
#> ── p21bridge findings ──────────────────────────────────────────────────────────
#> ℹ 12 findings from findings.csv
#> ℹ Severity: Error 7 | Warning 3 | Notice 2
#> ℹ Rule class: SDTM data 8 | Controlled terminology 2 | Define-XML 1 | ODM / define metadata 1
#> # A tibble: 12 × 8
#>    rule_id severity domain variable message            count rule_class category
#>    <chr>   <chr>    <chr>  <chr>    <chr>              <int> <chr>      <chr>   
#>  1 SD0056  Error    DM     RFENDTC  SDTM Required var…     1 SDTM data  Metadata
#>  2 SD1310  Warning  DM     USUBJID  Variable length i…     5 SDTM data  Length  
#>  3 CT2001  Error    DM     SEX      SEX value not fou…     1 Controlle… Termino…
#>  4 CT2001  Error    AE     AESEV    AESEV value not f…     2 Controlle… Termino…
#>  5 SD1011  Error    DM     RFSTDTC  Invalid ISO 8601 …     1 SDTM data  Format  
#>  6 SD1117  Warning  AE     USUBJID  Duplicate records      2 SDTM data  Consist…
#>  7 SD0064  Error    AE     USUBJID  Subject is not pr…     1 SDTM data  Cross-r…
#>  8 SD0002  Error    AE     AESTDTC  NULL value in var…     1 SDTM data  Presence
#>  9 SD1234  Notice   VS     VSORRES  Result value is n…     3 SDTM data  Consist…
#> 10 DD0011  Warning  DM     AGE      Variable label mi…     1 Define-XML Metadata
#> 11 SD1310  Error    ADSL   TRT01P   Variable length i…     4 SDTM data  Length  
#> 12 OD0018  Notice   NA     NA       Define-XML is mis…     1 ODM / def… Metadata
table(f$rule_class)
#> 
#> Controlled terminology             Define-XML  ODM / define metadata 
#>                      2                      1                      1 
#>              SDTM data 
#>                      8 

# The same report as .xlsx, with a different set of column names,
# normalises to the same thing.
xl <- system.file("extdata", "findings.xlsx", package = "p21bridge")
identical(read_findings(xl)$rule_id, f$rule_id)
#> [1] TRUE