CDR Tickets

BZDATETIME::2011-12-08 17:10:01
BZCOMMENTOR::Volker Englisch
BZCOMMENT::1

Question:

> 1. Glossary Terms (Patient) displayed on cancer.gov
> ...
> Please provide separate counts for English and Spanish Terms.

I'm not sure what you mean with separate "counts".
Would you like an
(a) English only
and
(b) Spanish only report
or sorted by language?

> 3. Glossary Terms (HP) published on cancer.gov
> ...
> We will also want separate counts for English
> and Spanish terms for this report.

Same question as for (1).

BZDATETIME::2011-12-12 12:24:20
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::2

(In reply to comment #1)
> Question:
>
> > 1. Glossary Terms (Patient) displayed on cancer.gov
> > ...
> > Please provide separate counts for English and Spanish Terms.
>
> I'm not sure what you mean with separate "counts".
> Would you like an
> (a) English only
> and
> (b) Spanish only report
> or sorted by language?
>
We need English only count and Spanish only count. It doesn't need to be sorted.
>
> > 3. Glossary Terms (HP) published on cancer.gov
> > ...
> > We will also want separate counts for English
> > and Spanish terms for this report.
>
> Same question as for (1).

Just like above, English only count and Spanish only count.

BZDATETIME::2011-12-12 12:31:41
BZCOMMENTOR::Volker Englisch
BZCOMMENT::3

William, a "count" in SQL is actually just a single number as in "Can you give me the count of all summary documents on Cancer.gov?" Answer: 594.

There are no columns involved and there is no sorting. I am confused because you said you wanted me to "display the same columns as in the existing ad hoc report" as to what exactly you'd like me to display for reports (1) and (3).

BZDATETIME::2011-12-12 12:45:12
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::4

How about creating separate queries for the counts so that for (1), you would have one query displaying the glossary terms (Both English and Spanish). Then in separate queries, we would want to know the number of English Glossary terms displayed on cancer.gov and also another query for the number of Spanish Glossary terms displayed on cancer.gov. Would this work?

BZDATETIME::2011-12-12 13:16:38
BZCOMMENTOR::Volker Englisch
BZCOMMENT::5

Are you aware that the number of records retrieved is listed at the bottom of every output? If that information is enough we could just create one query for English and one query for Spanish and you get your count from the last row of the output.

BZDATETIME::2011-12-12 13:28:11
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::6

(In reply to comment #5)
> Are you aware that the number of records retrieved is listed at the bottom of
> every output? If that information is enough we could just create one query for
> English and one query for Spanish and you get your count from the last row of
> the output.

Yes I am aware of the rows reported at the end of each query. However, there may be English terms without Spanish translations so using the rows alone would not give an accurate count of the Spanish terms unless you will be able to suppress these terms from the query for the Spanish terms.

BZDATETIME::2011-12-12 14:05:23
BZCOMMENTOR::Volker Englisch
BZCOMMENT::7

I've created the two queries named
Glossary Terms displayed on Cancer.gov (Patient-EN)
and
Glossary Terms displayed on Cancer.gov (Patient-ES)
which should display the correct count at the end of the query.

Please have a look and let me know if this is what you were looking for. If this is OK I'll recreate the same queries for HP.

BZDATETIME::2011-12-12 15:37:00
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::8

(In reply to comment #7)
> Please have a look and let me know if this is what you were looking for. If
> this is OK I'll recreate the same queries for HP.

Yes. They look great. Please proceed with the HP queries.

BZDATETIME::2011-12-13 10:54:50
BZCOMMENTOR::Volker Englisch
BZCOMMENT::9

(In reply to comment #0)
> These terms typically don't have any values
> for the Dictionary element in the GTC.

Could you give me an example of such a term?

BZDATETIME::2011-12-13 14:15:27
BZCOMMENTOR::Volker Englisch
BZCOMMENT::10

(In reply to comment #8)
> Please proceed with the HP queries.

The two HP queries for English and Spanish have been save as ad-hoc queries as well. The names of the queries created are:
Glossary Terms displayed on Cancer.gov (HP-EN)
Glossary Terms displayed on Cancer.gov (HP-ES)
Glossary Terms displayed on Cancer.gov (Patient-EN)
Glossary Terms displayed on Cancer.gov (Patient-ES)
Glossary Terms without Pronunciation

Please verify on BACH.

BZDATETIME::2011-12-13 14:46:22
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::11

(In reply to comment #9)
> (In reply to comment #0)
> > These terms typically don't have any values
> > for the Dictionary element in the GTC.
>
> Could you give me an example of such a term?

CDR0000270731 - 3-dimensional conformal radiation therapy;radioterapia conformal tridimensional [es]

CDR0000619686 - concept.

It appears there are no exclusively HP terms by my definition because even in the the above example, there are multiple definitions one for HP and one for Patient.

BZDATETIME::2011-12-14 13:36:01
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::12

(In reply to comment #10)
> (In reply to comment #8)
> > Please proceed with the HP queries.
>
> The two HP queries for English and Spanish have been save as ad-hoc queries as
> well. The names of the queries created are:
> Glossary Terms displayed on Cancer.gov (HP-EN)
> Glossary Terms displayed on Cancer.gov (HP-ES)
> Glossary Terms without Pronunciation

> Please verify on BACH.

The HP reports are missing the none genetics HP terms (those with multiple definitions).
Examples :

stereotactic radiosurgery (GTN 46701; GTC 619386)

stereotactic radiation therapy (GTN 44464; GTC 619590)

charged-particle therapy (GTN 534234; GTC 623765)

intensity-modulated radiation therapy (GTN 335073; GTC 620189)

3-dimensional conformal radiation therapy (GTN 270731; GTC 619686)

BZDATETIME::2011-12-14 15:27:56
BZCOMMENTOR::Volker Englisch
BZCOMMENT::13

I think I found and fixed the problem of those missing records.
Would you like to have another look on BACH?

BZDATETIME::2011-12-15 10:53:21
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::14

The HP-ES report is retrieving duplicate records. For example:
44463 stereotaxic radiosurgery radiocirugía estereotáxica 619386 ES Health professional
45854 radiation surgery cirugía por radiación 619386 ES Health professional

The HP-EN report is displaying all HP terms, including the genetics terms. It seems the HP-ES is working the way we would want the HP-EN to work besides retrieving duplicates.

BZDATETIME::2011-12-30 13:57:42
BZCOMMENTOR::Volker Englisch
BZCOMMENT::15

I fixed that HP-ES query to remove the duplicates.

Please have another look and close this issue if OK.

BZDATETIME::2012-01-04 16:36:25
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::16

(In reply to comment #15)
> I fixed that HP-ES query to remove the duplicates.
>
> Please have another look and close this issue if OK.

The HP-ES seems to be working as expected but not the HP-EN.
The HP-EN continues to have duplicates, for example (44452) and it continues to display terms with a dictionary element of Genetics.

BZDATETIME::2012-01-05 11:41:35
BZCOMMENTOR::Volker Englisch
BZCOMMENT::17

I never made any changes to the HP-EN version since I did not realize that it had the same problems as the HP-ES version.

I've now included the same fix for both queries.

Please verify on BACH and close this bug if it's OK.

BZDATETIME::2012-01-05 17:13:31
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::18

(In reply to comment #17)
> I never made any changes to the HP-EN version since I did not realize that it
> had the same problems as the HP-ES version.
>
The HP-EN still shows genetics terms (those with a dictionary value of Genetics). They should not show up on this report since they have their own report. This report should only show non-genetics HP terms (these would not have a Dictionary element).

BZDATETIME::2012-01-05 17:18:11
BZCOMMENTOR::Volker Englisch
BZCOMMENT::19

How many records do you retrieve? I do not see a single record with a genetics dictionary term when I'm running the report.

I have 263 records for the HP-EN report and 66 for the HP-ES report.

BZDATETIME::2012-01-06 09:13:35
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::20

(In reply to comment #19)
> How many records do you retrieve? I do not see a single record with a genetics
> dictionary term when I'm running the report.
>
> I have 263 records for the HP-EN report and 66 for the HP-ES report.

For the HP-EN report. Here are some of the Genetics terms that should not be there:

339337
339338
339339
339341
339342
339343
339344
339348
425374
The above examples all use the Genetics dictionary. The terms below are examples in this report (which shouldn't be there) which use both Genetics and Cancer.gov dictionary elements. This report is meant to capture only HP terms without a dictionary element so they should all be excluded.
339347
390290
454769

BZDATETIME::2012-01-06 13:55:23
BZCOMMENTOR::Volker Englisch
BZCOMMENT::21

Thank you for the sample IDs. I had to rewrite parts of the query but I'm hoping that the result is now what you're expecting.

Please check it out on BACH.

BZDATETIME::2012-01-09 10:26:30
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::22

(In reply to comment #21)
> Thank you for the sample IDs. I had to rewrite parts of the query but I'm
> hoping that the result is now what you're expecting.
>
> Please check it out on BACH.

This looks good. Thank you! I am closing the issue now.