search

lzim / teampsd

Team PSD is using GitHub, R and RMarkdown as part of our free and open science workflow.
GNU General Public License v3.0
15 stars 23 forks source link

wk? + Task: Add VVC to the Facility file #1638

Closed anazariz closed 3 years ago

anazariz commented 3 years ago

Add the new VVC filter, modeled after the Data UI, to the Facility.xlsb file

header ticket #1618

anazariz commented 3 years ago

@lzim, @staceypark @lijenn @anthonycpichardo

The Facility.xlsb file monthly refresh process consists of two parts.

  1. a script that has to be manually run once a month to create several PTSD_OMHO database tables
  2. the vba within the Facility.xlsb, which must also be manually run after step 1 to use the mentioned database tables to populate the Excel data sheets (data tables) as well as the Excel viz sheets (pivot tables).

To be able to incorporate VVC in the vizEnc sheet as a new filter, the VVC field must be added to the Excel dataDiag table. Following is the course of action I took: 1) I added the VVC field to the database script successfully (see the script for details) 2) I added the VVC field (from the related database table) to the Excel dataDiag table 3) I then proceeded to add the new VVC column (in the Excel dataDiag to the vizEnc's Pivot table (to be able to then add it to the sheets filters) 4) vizEnc's PivotTable Fields consists of two Excel tables, dataDiag and dataEnc. These two tables must be related. The relationship requires a main table and a looked up table (similar to a parent/child relationship). The link between the tables is visitsid. The table that is doing the searching can have non-unique visitsids. But the one that is being looked up must have unique visitsids. In order to get the pivot table see the new VVC field, the pivot table must be refreshed. When I refreshed the pivot table, I got an error saying that the looked up table had a duplicated visitsid. When I ran the query that builds the table (using SQL Server Management Studio), I realized that neither of the tables have unique visitsids. I need to do some more analysis to figure out why the pivot table did not complain even before the VVC addition (in its current state). So the work is not yet complete.

Please note that the Facility.xlsb was already refreshed on time (without the VVC addition) in anticipation of potential complexities.

anthonycpichardo commented 3 years ago

Will we be rolling out this feature as part of the propagation @anazariz ?