topic Undefined function 'NZ' in expression. in QlikView

Undefined function 'NZ' in expression.

dhborchardt — Fri, 18 Mar 2011 19:29:18 GMT

I have a connection to an Access database. I click the select button and the query I want is not listed. I can get the query to show by removing a part of it. If I add that portion back in I will get the error "ErrorSource: Microsoft JET Database Engine, ErrorMsg: Undefined function 'NZ' in expression."

Does QlikView not like the NZ function? I have the SQL below with the the section that causes the problem in BOLD text. Maybe I should do this in the load scrip instead?

SELECT dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, NZ([PresentAtAdmit],'N') AS AbsDiagCodes
FROM dbo_AbsDrgDiagnoses
WHERE (((dbo_AbsDrgDiagnoses.RowUpdateDateTime)>#9/1/2009#));

Undefined function 'NZ' in expression.

Miguel_Angel_Baeyens — Fri, 18 Mar 2011 20:38:54 GMT

Hello,

Note that it's not related to QlikView rather to the driver you are using, since QlikView doesn't parse what you SELECT, so I'd recommend to do a LOAD preceding the SQL SELECT:

Table:LOAD dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, If(Len([PresentAtAdmit]) = 0, 'N', [PresentAtAdmit]) AS AbsDiagCodes; // If the value is empty/null return "N", otherwise, the valueSQL SELECT dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, [PresentAtAdmit]FROM dbo_AbsDrgDiagnosesWHERE (((dbo_AbsDrgDiagnoses.RowUpdateDateTime)>#9/1/2009#));

Hope that helps.

Undefined function 'NZ' in expression.

dhborchardt — Fri, 18 Mar 2011 20:51:36 GMT

Thanks, that worked