A large difference between the values ​​in the child table about sdv

[npatki]

Hi @shirtevet, nice to meet you. We'd love to look into this further with you. Could you please share the metadata JSON that you are working with for this dataset?

If the data itself is able to be shared (or publicly available) that would be helpful as well. But at least with the metadata, we can ensure our investigations are matching your schema.

[shirtevet]

import json

metadata8 = {

"tables": {

"visits_data": {

"primary_key": "PATIENTID_VISITDATE",

"columns": {

"PATIENTID": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}"},

"PATIENTID_VISITDATE": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}_\d{2}-\d{2}-\d{4}"},

"VISITDATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" },

"VISITTYPECODE": { "sdtype": "categorical" },

"NOWPREGNANT": { "sdtype": "categorical" },

"FAMILYPLANNINGID": { "sdtype": "categorical" },

"FUNCTIONALSTATUSCODE": { "sdtype": "categorical" },

"WHOSTAGE": { "sdtype": "categorical" },

"TBSTATUSCODE": { "sdtype": "categorical" },

"IPTREASONCODE": { "sdtype": "categorical" },

"ARVSTATUSCODE": { "sdtype": "categorical" },

"ARVREASONCODE": { "sdtype": "categorical" },

"ARVCODE": { "sdtype": "categorical" },

"NOTES": { "sdtype": "text" },

"STAFFID": { "sdtype": "categorical" },

"USERNUMBER": { "sdtype": "categorical" },

"THETIMESTAMP": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" },

"IMPORTFILE": { "sdtype": "categorical" },

"FACILITY": { "sdtype": "categorical" },

"ENTRY_DATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" }

}

},

"test_data": {

"primary_key": "ID",

"columns": {

"ID": { "sdtype": "id" },

"PATIENTID_VISITDATE": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}_\d{2}-\d{2}-\d{4}" },

"PATIENTID_VISITDATE_ID": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}\d{2}-\d{2}-\d{4}\d+" },

"PATIENTID": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}" },

"TESTTYPEID": { "sdtype": "categorical" },

"TESTDATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" },

"RESULTDATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" },

"RESULTNUMERIC": { "sdtype": "numerical" },

"RESULTNOTES": { "sdtype": "text" },

"IMPORTFILE": { "sdtype": "categorical" },

"FACILITY": { "sdtype": "categorical" },

"ENTRY_DATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" }

}

},

"medication_data": {

"primary_key": "PATIENTID_VISITDATE_DRUGTYPEID",

"columns": {

"PATIENTID_VISITDATE": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}_\d{2}-\d{2}-\d{4}" },

"PATIENTID_VISITDATE_DRUGTYPEID": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}\d{2}-\d{2}-\d{4}[A-Z]+" },

"PATIENTID": { "sdtype": "id", "regex_format": "\d{2}-\d{2}-\d{1}[A-Z]-\d{4}-[A-Z]-\d{5}" },

"VISITDATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" },

"DRUGTYPEID": { "sdtype": "categorical" },

"QUANTITYDISPENSED": { "sdtype": "numerical" },

"PERCENTADHERENCE": { "sdtype": "numerical" },

"NOTES": { "sdtype": "text" },

"IMPORTFILE": { "sdtype": "categorical" },

"FACILITY": { "sdtype": "categorical" },

"ENTRY_DATE": { "sdtype": "datetime", "datetime_format": "%Y-%m-%d" }

}

}

},

"relationships": [

{

"parent_table_name": "visits_data",

"child_table_name": "test_data",

"parent_primary_key": "PATIENTID_VISITDATE",

"child_foreign_key": "PATIENTID_VISITDATE"

},

{

"parent_table_name": "visits_data",

"child_table_name": "medication_data",

"parent_primary_key": "PATIENTID_VISITDATE",

"child_foreign_key": "PATIENTID_VISITDATE"

}

]

}