Are you sure the LDL and Triglycerides fields contain values too? You don't check for nulls there. Does the expression work in a straight table?
BTW, I think you can use the alt function instead of the OR checks:
AND [Total Cholesterol]>0
AND [Total Cholesterol]<5000
AND HDL<[Total Cholesterol]
AND ((HDL+LDL+(Triglycerides/5))<=([Total Cholesterol]+4)),
[ZCholesterol Ratio]),.1) as [Cholesterol Ratio]
Hi Gysbert, some records did and some didn't have ldl and triglyceride values; and neither is getting the Cholesterol Ratio which had me scratching my head. I've been scrutinizing timestamps and things to see if anything is keeping them off the same row in the background or whatnot and found nothing there. I figured that last check would treat the nulls as 0s or not worry about them. I actually added the +4 to let negligible rounding errors through when I saw an otherwise perfectly good record overshoot the expected total by a couple of points, and the cr still wouldn't calculate.
I'm not a doctor, but I think medically it is possible for people to measure total cholesterol, then hdl, and maybe not have done tests for or recorded ldl and triglyceride values. That last check was just a smell test to see if anything crazy was in the data and skip that record if so. (I didn't want to populate new values with bad data and make more bad data.)
I'm going to try dropping that last check or maybe some simpler ways of writing out the expression to see what happens. (There are filters checking for humanly possible values on individual metrics coming in, so there shouldn't be too much bad left standing.) Thanks for the Alt function too.
Oh yeah, everything works fine when I do it in excel, and I expect it would work fine if I just did a this/that expression on a straight table in qlikview. Effort to make it a complete field in script was more for best practice sake of not having to use straight tables to finish populating values in a field.