| Issue Number | 3060 |
|---|---|
| Summary | [CTGov Transfer] Ad hoc query for transfer trials marked as duplicates |
| Created | 2010-01-07 17:19:00 |
| Issue Type | Improvement |
| Submitted By | Osei-Poku, William (NIH/NCI) [C] |
| Assigned To | Kline, Bob (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2010-01-13 15:49:17 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.107388 |
BZISSUE::4736
BZDATETIME::2010-01-07 17:19:00
BZCREATOR::William Osei-Poku
BZASSIGNEE::Bob Kline
BZQACONTACT::William Osei-Poku
We need an ad hoc query to identify trials that have been marked for
transfer but no converted documents were created for them for reasons
other than not meeting the criteria for importing trials from the NLM.
(We already have a query that identifies trials that do not meet the
selection criteria). What we are looking for are:
i. Trials that have the transfer blocks
ii. But do not have corresponding (converted) CTGov trials
iii. Because they either have duplicate records (NCT IDs)
iv. Or for any other reason for which no new CTGov trials were created
for them.
BZDATETIME::2010-01-07 17:32:41
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::1
Added Lakshmi and Margaret(I forgot to include them in the initial post).
Bob:
On Mahler, the query is still not picking up any protocols.
BZDATETIME::2010-01-07 17:48:16
BZCOMMENTOR::Bob Kline
BZCOMMENT::2
The query has been fixed (on Mahler and Bach). However the criteria you gave when you created the issue for this task are not the same as the ones discussed with Lakshmi in the status meeting (nor do they match the ad-hoc query title we selected when we worked on the query together after the meeting). The query I created finds InScopeProtocols which have the transfer blocks and which are marked as duplicates in the ctgov_import table, which is what I heard Lakshmi give as the logic needed for the report.
BZDATETIME::2010-01-08 11:44:42
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::3
(In reply to comment #2)
> The query has been fixed (on Mahler and Bach). However the criteria
you gave
> when you created the issue for this task are not the same as the
ones discussed
> with Lakshmi in the status meeting (nor do they match the ad-hoc
query title we
> selected when we worked on the query together after the meeting).
The query I
> created finds InScopeProtocols which have the transfer blocks and
which are
> marked as duplicates in the ctgov_import table, which is what I
heard Lakshmi
> give as the logic needed for the report.
I copied my original request (from my email) and posted it in the original post in this issue. That is why you noticed the discrepancies. I think what Lakshmi articulated was what it finally came up to after explaining what we needed.
On the other hand, considering the failure email notification we received today for which you explained that the document was checked out to the user, if all failures which results in non-conversion would be reported for us to investigate, we will not need the query. If the conversion software reports all failures regardless of the cause, the results will include the scenario we are trying to get the query for.
BZDATETIME::2010-01-08 11:58:25
BZCOMMENTOR::Bob Kline
BZCOMMENT::4
(In reply to comment #3)
> On the other hand, considering the failure email notification we
received today
> for which you explained that the document was checked out to the
user, if all
> failures which results in non-conversion would be reported for us
to
> investigate, we will not need the query. If the conversion software
reports all
> failures regardless of the cause, the results will include the
scenario we are
> trying to get the query for.
No, that scenario doesn't represent a conversion failure at all. In that scenario, the trial document from NLM doesn't get queued up for import at all, because CIAT has told the download job to skip it (by telling the ctgov_import table that it's a duplicate), so it never gets to the import/conversion job.
BZDATETIME::2010-01-08 12:45:56
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::5
(In reply to comment #4)
> (In reply to comment #3)
>
> No, that scenario doesn't represent a conversion failure at all. In
that
> scenario, the trial document from NLM doesn't get queued up for
import at all,
> because CIAT has told the download job to skip it (by telling the
ctgov_import
> table that it's a duplicate), so it never gets to the
import/conversion job.
OK. Thanks! I think the query as it is written is still useful. So we will make use of it and if we need a modification later, I will enter another request. There was another 'failure' from yesterday's transfer 389159 which resulted in a non-converted document. The current query reported 389159. It is a trial in this type of situation that we want to know about.
BZDATETIME::2010-01-13 15:48:25
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::6
Marked as resolved.
BZDATETIME::2010-01-13 15:49:17
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::7
(In reply to comment #6)
> Marked as resolved.
Issue closed. Thank you!
Elapsed: 0:00:00.001269