topic Expression to populate a missing ratio when underlying values are present and reasonable in QlikView

Expression to populate a missing ratio when underlying values are present and reasonable

stevelord — Thu, 15 Jan 2015 19:32:36 GMT

Hi, can someone tell me what might be off with this expression?

All of the data is present in one table, and I see the component values side-by-side in the table. I could write an expression on the straight table, but want it in the script so I don't have to update various tables. The below expression lets the source values through, but isn't populating the missing values like I'd like. I have this working okay for BMI when height/weight are present, but below is a bit more complicated on its validation effort.

Cholesterol Ratio is just Total Cholesterol divided by HDL. My expression below is evaluating the source data's cholesterol ratios [ZCholesterol Ratio] at that stage) and if it's missing or impossible for a user, it then checks to see if Total Cholesterol and HDL are present and possible, and a little check to be sure the components of Total Cholesterol don't add up to more than the Total Cholesterol. (I only wanted to populate missing values if the data in the calculation was reasonably clean.)

No syntax errors, and the script runs okay. I'm just not seeing the missing cholesterol ratios populated and will be calculating them in excel for the moment.

Load *,

Round(if(([ZCholesterol Ratio]<1 OR len([ZCholesterol Ratio])=0 OR [ZCholesterol Ratio]='') AND [Total Cholesterol]>0 AND [Total Cholesterol]<5000

AND HDL>1 AND HDL<[Total Cholesterol] AND ((HDL+LDL+(Triglycerides/5))<=([Total Cholesterol]+4)), [Total Cholesterol]/HDL, [ZCholesterol Ratio]),.1) as [Cholesterol Ratio]

//the +4 near the end of this statement is to allow for very sleight rounding errors on each of the three component values.

Re: Expression to populate a missing ratio when underlying values are present and reasonable

Gysbert_Wassenaar — Fri, 16 Jan 2015 09:35:10 GMT

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:

Load *,

Round(if(Alt([ZCholesterol Ratio],1)<1

AND [Total Cholesterol]>0

AND [Total Cholesterol]<5000

AND HDL>1

AND HDL<[Total Cholesterol]

AND ((HDL+LDL+(Triglycerides/5))<=([Total Cholesterol]+4)),

[Total Cholesterol]/HDL,

[ZCholesterol Ratio]),.1) as [Cholesterol Ratio]

Re: Expression to populate a missing ratio when underlying values are present and reasonable

stevelord — Fri, 16 Jan 2015 15:51:11 GMT

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.

Re: Expression to populate a missing ratio when underlying values are present and reasonable

stevelord — Fri, 16 Jan 2015 15:55:41 GMT

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.