Label Variables with Descriptive Values and Create Features
convert catagorical data types to string
Text
IF [_CurrentField_] == "0" THEN "no" ELSEIF [_CurrentField_] == "1" THEN "yes" ELSE null() ENDIF
modify field to descriptive values
IF [_CurrentField_] == "0" THEN "no" ELSEIF [_CurrentField_] ...
Modify variables to descriptive values
phase = IF [phase] == "0" THEN "discordant" ELSE [phase] ENDIF
sponsor_status = ...
[completionyear] != 2000 and [completionyear] != 2019 and [completionyear] != 2041
Custom
=
True
fixed
2018-09-25 12:36:40
0
2018-09-25 12:36:40
2018-09-25 12:36:40
Exclude data with bad completion dates. Year = 2000, 2019 or 2041
[completionyear] != 2000 and [completionyear] != 2019 and [completionyear] != 20...
url
SpecificFile
file
GET
Compose
2
600
https://github.com/ebmdatalab/euctr-tracker-data/blob/master/data_for_paper/EUCTR%20Data_17Jan2018.xlsx?raw=true
C:\ProgramData\Alteryx\Engine\Engine_10892_724d06f4552f4df996874b680e6722b0_\File.xlsx|||`Final Data$`
False
1
ReadList
file
Path
Data Investigation - Missing End Dates
Trials with no completion date count
[phase] == "1" AND [includes_pip] == "no"
Custom
=
True
fixed
2018-09-25 14:23:43
0
2018-09-25 14:23:43
2018-09-25 14:23:43
[phase] == "1" AND [includes_pip] == "no"
IsNull([max_end_date])
Simple
IsNull
max_end_date
True
fixed
2018-10-02 13:16:34
days
0
2018-09-25
2018-10-02 13:16:34
2018-10-02 13:16:34
IsNull([max_end_date])
C:\ProgramData\Alteryx\Engine\Engine_16720_a7721c3264134be287ab73543a2a3ada_\Engine_4092_a62bb0f00c014dd0a8f65b706bcfa302_.yxdb
Single
Profile
[trial_status] = "all_complete"
Simple
=
trial_status
True
fixed
2018-09-25 14:26:21
0
all_complete
2018-09-25 14:26:21
2018-09-25 14:26:21
[trial_status] = "all_complete"
Table 3 | Logistic Regression Analysis
[completionyear] < 2005
Simple
<
completionyear
True
fixed
2018-09-25 14:52:52
0
2005
2018-09-25 14:52:52
2018-09-25 14:52:52
[completionyear] < 2005
C:\ProgramData\Alteryx\Engine\Engine_7444_d219c4c13d2647db950e091e9da77d99_\Engine_3136_c0881cef3b354d02aeadd6da9af79c6e_.yxdb
Single
Profile
C:\ProgramData\Alteryx\Engine\Engine_7444_d219c4c13d2647db950e091e9da77d99_\Engine_3136_d136cfc018ed4bd4aed24846b443ba90_.yxdb
Single
Report
C:\ProgramData\Alteryx\Engine\Engine_7444_d219c4c13d2647db950e091e9da77d99_\Engine_3136_a1655ae7d9a24529a553fd5ae284f216_.yxdb
Single
Report
Home
advanced
True
False
Logistic_Regression_76
results
includes_pip,rare,phase,bioequivalence_study,health_volunteers,all_terminated,sponsor_status,sponsor_trial_count,multiple_sponsors,country_count,bad_sponsor,quartile
False
False
0.5
False
False
5
lambda_1se
False
1
0.5
logit
False
5
3
False
False
1
1x
Logistic_Regression_76
EqualRecords
sponsor_trial_count
Equal Records
Tables 4 and 5 | Compliance by Sponsor
C:\ProgramData\Alteryx\Engine\Engine_7444_d219c4c13d2647db950e091e9da77d99_\Engine_3136_718c1d40c96740cd91a7ace05842837a_.yxdb
Single
Profile
Error
!IsNull([Due trials]) AND [Total trials on EUCTR] > 50
Custom
IsNotNull
Due trials
True
fixed
2018-09-27 09:48:08
0
2018-09-27 09:48:08
2018-09-27 09:48:08
!IsNull([Due trials]) AND [Total trials on EUCTR] > 50
% reported - Descending
Total trials on EUCTR - Descending
% reported - Ascending
Total trials on EUCTR - Descending
C:\ProgramData\Alteryx\Engine\Engine_7444_d219c4c13d2647db950e091e9da77d99_\Engine_3136_acf73873f8914b9aae6dfd3161082ba6_.yxdb
Single
Profile
Table 2 | Reporting rates in each cohort, by category of trial
[results] = "yes"
Simple
=
results
True
fixed
2018-09-27 08:21:35
0
yes
2018-09-27 08:21:35
2018-09-27 08:21:35
[results] = "yes"
<Field field="results" selected="True" /><Field field="includes_pip" selected="True" /><Field field="rare" selected="True" /><Field field="phase" selected="True" /><Field field="bioequivalence_study" selected="True" /><Field field="trial_status" selected="True" /><Field field="all_terminated" selected="True" /><Field field="sponsor_status" selected="True" /><Field field="multiple_sponsors" selected="True" /><Field field="country_count" selected="True" /><Field field="completionyear" selected="True" /><Field field="bad_sponsor" selected="True" /><Field field="*Unknown" selected="False" />
<Field field="results" selected="True" /><Field field="includes_pip" selected="True" /><Field field="rare" selected="True" /><Field field="phase" selected="True" /><Field field="bioequivalence_study" selected="True" /><Field field="trial_status" selected="True" /><Field field="all_terminated" selected="True" /><Field field="sponsor_status" selected="True" /><Field field="multiple_sponsors" selected="True" /><Field field="country_count" selected="True" /><Field field="completionyear" selected="True" /><Field field="bad_sponsor" selected="True" /><Field field="*Unknown" selected="False" />
proportion = [Trials with results]/[Total trials]
% with results (95% CI) = ToSt...
Basic
100%
Basic Table
C:\ProgramData\Alteryx\Engine\Engine_772_a6e90ef746d240e5ac87dba88c23ebea_\Engine_3136_6df36f8c90dd4051b01caa5a8be3460f_.yxdb
Single
Report
Basic
100%
Basic Table
PerRun
V
Continuous
100%
0
Vertical Layout
Merge Content Tables
All Records Combined
Text
[_CurrentFieldName_]
[_CurrentFieldName_]
First
1
First 1
Basic
100%
Basic Table
Warning
All
ByName
Variables - Ascending
Data Investigation - Field Summary
Basic Data Investigation with Field Summary Tool (check for missing values, distribution, etc.)
trial_id=False,number_of_countries=True,min_end_date=True,max_end_date=True,comp_date=True,results=True,includes_pip=True,rare=True,phase=True,bioequivalence_study=True,health_volunteers=True,trial_status=True,any_terminated=True,all_terminated=True,sponsor_status=True,name_of_sponsor=True,normalized_name_only=True,sponsor_trial_count=True,sponsor_count=True,multiple_sponsors=True,country_count=True,completionyear=True,bad_sponsor=True
False
True
5000
False
10
Data Investigation - Date Inconsistencies
difference between maximum and minimum end dates
count number of missing end dates
dateddifference = If DateTimeDiff([max_end_date], [min_end_date], "days") = 0 TH...
C:\ProgramData\Alteryx\Engine\Engine_14388_d80bdbedbe9a4b478128cf37abb96f13_\Engine_4092_eaf98b8f0c644eff8cef13d3ecd8a00a_.yxdb
Single
Profile
[trial_status] = "all_complete"
Simple
=
trial_status
True
fixed
2018-09-25 14:18:05
0
all_complete
2018-09-25 14:18:05
2018-09-25 14:18:05
[trial_status] = "all_complete"
C:\ProgramData\Alteryx\Engine\Engine_14388_d80bdbedbe9a4b478128cf37abb96f13_\Engine_4092_74d1d494af6745449943a1088bb51707_.yxdb
Single
Profile
Table 1 | Characteristics of included trials
<Field field="results" selected="True" /><Field field="includes_pip" selected="True" /><Field field="rare" selected="True" /><Field field="phase" selected="True" /><Field field="bioequivalence_study" selected="True" /><Field field="trial_status" selected="True" /><Field field="all_terminated" selected="True" /><Field field="sponsor_status" selected="True" /><Field field="multiple_sponsors" selected="True" /><Field field="country_count" selected="True" /><Field field="completionyear" selected="True" /><Field field="bad_sponsor" selected="True" /><Field field="*Unknown" selected="False" />
<Field field="results" selected="True" /><Field field="includes_pip" selected="True" /><Field field="rare" selected="True" /><Field field="phase" selected="True" /><Field field="bioequivalence_study" selected="True" /><Field field="trial_status" selected="True" /><Field field="all_terminated" selected="True" /><Field field="sponsor_status" selected="True" /><Field field="multiple_sponsors" selected="True" /><Field field="country_count" selected="True" /><Field field="completionyear" selected="True" /><Field field="bad_sponsor" selected="True" /><Field field="*Unknown" selected="False" />
Total cohort (31818 trials) = ToString([Frequency]) + " (" + ToString([Percent],...
Basic
100%
Basic Table
C:\ProgramData\Alteryx\Engine\Engine_18144_5489698e509a457a8e48092361b63a73_\Engine_3136_c342d05ea9804ef7be8079fb71822fbe_.yxdb
Single
Report
Basic
100%
Basic Table
PerRun
V
Continuous
100%
0
Vertical Layout
Merge Content Tables
All Records Combined
Text
[_CurrentFieldName_]
[_CurrentFieldName_]
First
1
First 1
Basic
100%
Basic Table
Warning
All
ByName
Variables - Ascending
Total with results due (7274 trials) = ToString([Frequency]) + " (" + ToString([...
Summary of Missing Due Reports
C:\ProgramData\Alteryx\Engine\Engine_9236_cee5421b515a41f790386fae04997bd9_\Engine_17536_93f1362d787246268cb60894e618ee9e_.yxdb
Single
Profile
Error
proportion = [Trials]/[Total Trials]
% with results (95% CI) = ToString(100*[Tri...
Make "Results Due" STUDY COHORT
[trial_status] = "all_complete"
Simple
=
trial_status
True
fixed
2018-09-25 14:34:56
0
all_complete
2018-09-25 14:34:56
2018-09-25 14:34:56
keep only trials where all countries complete
[trial_status] = "all_complete"
[max_end_date] > "2016-12-19"
Simple
>
max_end_date
True
fixed
2018-09-25 14:40:54
days
0
2016-12-19
2018-09-25 14:40:54
2018-09-25 14:40:54
drop if completion date is within past 12 months of research date (12/19/2016)
[max_end_date] > "2016-12-19"
[phase] == "1" AND [includes_pip] == "no"
Custom
=
True
fixed
2018-09-25 14:37:55
0
2018-09-25 14:37:55
2018-09-25 14:37:55
drop if phase 1, unless it is a paeds trial
[phase] == "1" AND [includes_pip] == "no"
!IsNull([max_end_date])
Simple
IsNotNull
max_end_date
True
fixed
2018-09-25 14:36:46
days
0
2018-09-25
2018-09-25 14:36:46
2018-09-25 14:36:46
drop if completion date is missing
!IsNull([max_end_date])
Figure 1 | Sankey Diagram
cond.install <- function(package.name){
options(repos = "http://cran.rstudio.com") #set repo
#check for package in library, if package is missing install
if(package.name%in%rownames(installed.packages())==FALSE) {
install.packages(package.name)}else{require(package.name, character.only = TRUE)}}
cond.install("networkD3")
library(networkD3)
library(AlteryxRviz)
nodes = data.frame("name" =
c("Trials on EUCTR (n=31821)",
"All completed or terminated (n=11531)",
" ",
"Bad date data (n=3)",
"Not all completed or terminated (n=20287)",
"Missing completion dates (n=3392)",
"Phase I (non-PIP) (n=325)",
"Not yet due (n=540)",
"Results due (n=7274)"))
links = as.data.frame(matrix(c(
0, 3, 3, # Each row represents a link. The first number
0, 4, 20827,
0, 1, 11531,
1, 5, 3392,
1, 6, 325,
1, 7, 540,
1, 8, 7274),# The third number is the value of the node
byrow = TRUE, ncol = 3))
names(links) = c("source", "target", "value")
# Add a 'group' column to each connection:
links$group=as.factor(c("type_a","type_a","type_a","type_a","type_a","type_a", "type_b"))
# Add a 'group' column to each node. Here I decide to put all of them in the same group to make them grey
nodes$group=as.factor(c("unique_group"))
# Give a color for each group:
my_color <- 'd3.scaleOrdinal() .domain(["type_a", "type_b", "unique_group"]) .range(["#79E9D7", "#FFC6C3", "#0D2345"])'
p <- sankeyNetwork(Links = links, Nodes = nodes,
Source = "source", Target = "target",
Value = "value", NodeID = "name",
colourScale=my_color, LinkGroup="group", NodeGroup="group",
fontSize= 14, nodeWidth = 15)
renderInComposer(p, nOutput= 3)
[html:A]
Raw PCXML
No Text Styling
C:\ProgramData\Alteryx\Engine\Engine_10392_b0d57846b87d4ad0b96c9a9cce6cfa6f_\Engine_3136_5f97be73106b460aa5a12a42909d3276_.yxdb
Single
Report
Data Investigation - Active Trials by Sponsor
Investigate Quantiles and Deciles of Number of Active Trials by Sponsor
EqualRecords
Equal Records
[sponsor_trial_count] > 11
Simple
>
sponsor_trial_count
True
fixed
2018-09-25 13:52:38
0
11
2018-09-25 13:52:38
2018-09-25 13:52:38
[sponsor_trial_count] > 11
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_3234fcd0ac7b44a599ca64ebc63603c5_.yxdb
Single
Profile
Warn
Warn
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_8e17f6f344a64f32bc4ec7d0596d97dc_.yxdb
Single
Profile
Unique: normalized_name_only, sponsor_trial_count
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_081639ff80ae4baeb37e01b389eb5311_.yxdb
Single
Profile
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_3f76e2f1c6f3480b90c9875c8826b559_.yxdb
Single
Profile
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_98cb100139a24d428ce37762f2685098_.yxdb
Single
Report
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_46bc1fc87d634c90b2effd9ec3c32227_.yxdb
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_7e4c687138404051bd7ba315805ad256_.yxdb
Single
Profile
<Field field="trial_status" selected="True" /><Field field="completionyear" selected="True" /><Field field="*Unknown" selected="False" />
EqualRecords
Equal Records
sponsor_trial_count
auto
False
1.0
True
5.50
5.50
False
13.00
14.95
1x
10
C:\ProgramData\Alteryx\Engine\Engine_16036_f106f34603134f999b12c6957590e69f_\Engine_4092_e63236e135904ca3a5d57ce25fd192d4_.yxdb
Single
Report
Horizontal
GoldacreReplication