3 Replies Latest reply: Apr 17, 2013 10:50 AM by Steve Lord RSS

    How to contatenate only certain values in a field, and insert related calculated values

    Steve Lord

      Hi, I have a field like this:

       

      TestName

      Systolic

      Diastolic

      BMI

      Height Ft

      Height In

      Weight

      etc...

       

      Within the field TestName, how do I concatenate Height Ft and Height In to a single Height FtIn  Value under that field.  Ditto for making a Systolic / Diastolic value in that column.

       

      Next, how do I have the associated values on that rows concatenated in the same way.  So 128.6 on the Systolic row and 90.5 on the Diastolic row come back to 128.6 / 90.5 on the Systolic / Diastolic row.

       

      TestName                    TestValue

      Systolic                         128.6

      Diastolic                         90.5

      Systolic / Diastolic          128.6 / 90.5

      BMI                                 29.15

      Height Ft                         5.13

      Height In                         5.6

      Height Ft/In                    5.13ft5.6in

      Weight

      etc...

       

      Lastly, how would I make a calculated value field that adds the decimal feet value to 12xdecimal inches value and converts that to a single standard ft/in value like 5ft7in.  (Excel converts the decimal feet right of the decimal to inches and concatenates back to the feet value left of the decimal.)

       

      This might seem easy if I use a generic load to make those values into fields and concatenate/calculate the heck out of them, but I have other weighted average calculated dimensions applied to these in a pivot table that looks real nice otherwise.  So I'd like to just sneak the customized systolic / diastolic and FtIn values if I can.  There are also millions of records involved, so juggling is limited.  (I crash my computer if I try to insert more columns than necessary on a chart object. )