Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
baylor2016
Contributor

Field cannot find error?

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')

1 Solution

Accepted Solutions

Re: Field cannot find error?

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')

5 Replies

Re: Field cannot find error?

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')

baylor2016
Contributor

Re: Field cannot find error?

Thank you for the trick!

Re: Field cannot find error?

No worries, glad you are sorted.

baylor2016
Contributor

Re: Field cannot find error?

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.

Re: Field cannot find error?

Probably best to start a new thread with some sample data.

Community Browser