P2 program quitting from lines 325-361

[CartWill]

From CHORDS-QA created by cathyhawkins: UCCC/CHORDS-QA#12

Quitting from lines 325-361 (P2.Rmd) Error in mutate_impl(.data, dots) : Evaluation error: character string is not in a standard unambiguous format. In addition: There were 27 warnings (use warnings() to see them)

getting this error for 2 of our 6 vdws

[CartWill]

I'm sorry I can't provide you access to our VDW. I already ran the sql query from the code on the prescribing table which is where I think the problem is. There are no issues running the code directly. Can you confirm that the error occurs at the prescribing table? Perhaps I'm focusing on the wrong table.

Cathy Hawkins | Senior Data Engineer

Colorado Community Managed Care Network

1212 S Broadway, Suite 200 | Denver, CO 80210

Direct 720.925.5281 | cathy@ccmcn.commailto:cathy@ccmcn.com

---

From: vtmacox notifications@github.com Sent: Friday, March 30, 2018 11:30 AM To: UCCC/CHORDS-QA Cc: Cathy Hawkins; Author Subject: Re: [UCCC/CHORDS-QA] P2 program quitting from lines 325-361 (#12)

Are you able to verify the dates are the same? Is there any way I could get access to the VDW to do some tests? Alternatively, you could use the sql query from the code to verify that the dates don't have strange values in them.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/UCCC/CHORDS-QA/issues/12#issuecomment-377576977, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AjuFN89SmdfpVKb6EvwY_OP8-RGxwnNMks5tjmupgaJpZM4S-5Ru.

[CartWill]

date formats are standard throughout all our vdws. I thought perhaps it was because we had future dates with these two, but after removing future dates the program still failed with the same error.

Cathy Hawkins | Senior Data Engineer

Colorado Community Managed Care Network

1212 S Broadway, Suite 200 | Denver, CO 80210

Direct 720.925.5281 | cathy@ccmcn.commailto:cathy@ccmcn.com

---

From: vtmacox notifications@github.com Sent: Friday, March 30, 2018 10:31 AM To: UCCC/CHORDS-QA Cc: Cathy Hawkins; Author Subject: Re: [UCCC/CHORDS-QA] P2 program quitting from lines 325-361 (#12)

Without seeing the data, I can't be sure, but it looks like the date values are not in a format that R can easily recognize. Given that this code works on other tables from other databases, is is possible that the date format varies from one VDW to another? If so, I would need to see the format of the dates from these other VDWs to fix the code.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/UCCC/CHORDS-QA/issues/12#issuecomment-377563901, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AjuFN2SLbRbcJAhPA_YPZ7_oWkBhAaHNks5tjl3JgaJpZM4S-5Ru.

[CartWill]

ok, well that helps.

turns out the problem is not a date issue, but a null issue, there is no data in the table.

I will figure out why there is no data and rerun. thanks for your help.

Cathy

Cathy Hawkins | Senior Data Engineer

Colorado Community Managed Care Network

1212 S Broadway, Suite 200 | Denver, CO 80210

Direct 720.925.5281 | cathy@ccmcn.commailto:cathy@ccmcn.com

---

From: vtmacox notifications@github.com Sent: Friday, March 30, 2018 11:43 AM To: UCCC/CHORDS-QA Cc: Cathy Hawkins; Author Subject: Re: [UCCC/CHORDS-QA] P2 program quitting from lines 325-361 (#12)

The error is actually coming from the Figure 1 LAB_RESULTS table. And the error is related to the date. The SQL query is :

select test_type, month(result_dt) as month, year(result_dt) as year, count(*) as nrows from lab_results where test_type in ('HGBA1C', 'PREGNANCY', 'HDL', 'LDL', 'TRIGLYCERIDE', 'GLUCOSE', 'CREATININE', 'HEP_C_RNA', 'HEP_C_ANTIBODY', 'HEP_C_GENOTYPE', 'IGE', 'EOSINOPHIL') group by year(result_dt), month(result_dt), test_type

Could you send me the results of the SQL query. The error is coming from the month and year variables from the query.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/UCCC/CHORDS-QA/issues/12#issuecomment-377579848, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AjuFN31xpJddYd4INrAT4A_Fpw6EuG6Bks5tjm60gaJpZM4S-5Ru.

[CartWill]

Are you able to verify the dates are the same? Is there any way I could get access to the VDW to do some tests? Alternatively, you could use the sql query from the code to verify that the dates don't have strange values in them.

[CartWill]

Without seeing the data, I can't be sure, but it looks like the date values are not in a format that R can easily recognize. Given that this code works on other tables from other databases, is is possible that the date format varies from one VDW to another? If so, I would need to see the format of the dates from these other VDWs to fix the code.

[CartWill]

The error is actually coming from the Figure 1 LAB_RESULTS table. And the error is related to the date. The SQL query is :

select test_type, 
    month(result_dt) as month,
    year(result_dt) as year,
    count(*) as nrows
      from lab_results
      where test_type in ('HGBA1C', 'PREGNANCY', 'HDL', 'LDL', 'TRIGLYCERIDE', 'GLUCOSE', 'CREATININE', 'HEP_C_RNA', 'HEP_C_ANTIBODY', 'HEP_C_GENOTYPE', 'IGE', 'EOSINOPHIL')
        group by year(result_dt), month(result_dt), test_type

Could you send me the results of the SQL query? The error is coming from the month and year variables from the query.