Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using the following script to load my data. The query runs ok in Server, but the load script always give me 'Field not found <EncounterAge>' error. It looks like the renamed or calculated fields cannot be used in load script. Is this true? If yes, what is the solution?
Thanks
Longmatch
Encounters:
LOAD *,
if(BMI<=18.5, 'Underweight', if(BMI<=24.9, 'Normal', if(BMI<=29.9, 'Overweight', 'Obesity'))) as BMI2,
if(BP_SYSTOLIC<140, '<140', '>=140') as Systolic,
if(BP_DIASTOLIC<90, '<90', '>=90') as Diastolic,
if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+'))) as Age2
;
sql
with DiabetesPatientList as (
select distinct a.pat_id
from problem_list a
where a.icd9_code like '250.%'
)
select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C, a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,
a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC, e.DEPARTMENT_NAME,
trunc ((a.contact_date - b.birth_date)/365.25) as EncounterAge,
c.Prov_name as PCP,
d.prov_name as VisitPhysician,
e.Department_NAME as Department,
extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth
from pat_enc a
inner join patient b on a.pat_id = b.pat_id
INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id
LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id
left join clarity_ser d on d.prov_id = a.visit_prov_id
left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID
where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy')
Many varieties of sql will capitilise field names unless enclosed in double quotes. Try this :
Encounters:
LOAD *,
if(BMI<=18.5, 'Underweight', if(BMI<=24.9, 'Normal', if(BMI<=29.9, 'Overweight', 'Obesity'))) as BMI2,
if(BP_SYSTOLIC<140, '<140', '>=140') as Systolic,
if(BP_DIASTOLIC<90, '<90', '>=90') as Diastolic,
if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+'))) as Age2
;
sql
with DiabetesPatientList as (
select distinct a.pat_id
from problem_list a
where a.icd9_code like '250.%'
)
select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C, a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,
a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC, e.DEPARTMENT_NAME,
trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",
c.Prov_name as PCP,
d.prov_name as VisitPhysician,
e.Department_NAME as Department,
extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth
from pat_enc a
inner join patient b on a.pat_id = b.pat_id
INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id
LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id
left join clarity_ser d on d.prov_id = a.visit_prov_id
left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID
where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy')
Many varieties of sql will capitilise field names unless enclosed in double quotes. Try this :
Encounters:
LOAD *,
if(BMI<=18.5, 'Underweight', if(BMI<=24.9, 'Normal', if(BMI<=29.9, 'Overweight', 'Obesity'))) as BMI2,
if(BP_SYSTOLIC<140, '<140', '>=140') as Systolic,
if(BP_DIASTOLIC<90, '<90', '>=90') as Diastolic,
if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+'))) as Age2
;
sql
with DiabetesPatientList as (
select distinct a.pat_id
from problem_list a
where a.icd9_code like '250.%'
)
select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C, a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,
a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC, e.DEPARTMENT_NAME,
trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",
c.Prov_name as PCP,
d.prov_name as VisitPhysician,
e.Department_NAME as Department,
extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth
from pat_enc a
inner join patient b on a.pat_id = b.pat_id
INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id
LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id
left join clarity_ser d on d.prov_id = a.visit_prov_id
left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID
where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy')
Thank you for the trick!
No worries, glad you are sorted.
There is another question. The script below does now work for the newly renamed field in load statement.
if(len(EncounterAge)=0, 'Null', if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+')))) as Age2
The len(EncounterAge)=0 already return false, since I cannot get Null even though there are a lot of blanks in this field.
Do I need start another thread?
Thanks.
Probably best to start a new thread with some sample data.