3 Replies Latest reply: Jan 16, 2015 10:55 AM by Steve Lord RSS

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

    Steve Lord

      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

          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
              Steve Lord

              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.