dividing field value on one row by a different field value on another row when related fields match

[stevelord]

Hi, please see below and advice.  Thanks!

The Total Cholesterol and the HDL values are on two separate lines under the TestValue heading in the original data, so when I try to divide one by the other it only picks up a null beside it and gives error or 1 or some total cholesterol value depending on the formula I try.  (I created the new CholRatioTotal and CholRatioHDL fields to house the number values after I used the other field names to house High/Low text comments.)

Source data and data on the Qlikview straight table looks like

ClientAccountName ExternalID TestDate CholRatioTotal CholRatioHDL     Cholesterol Ratio

aaa                              1234          1/1/13          190                    -                         //Error in calculated dimension

aaa                              1234          1/1/13          -                         50                      //Error in calculated dimension

So I end up with 190/- or -/50.  I'm trying AGGR() to see if that can collapse the CRTotal and CRHDL by the other fields in common, but I'm missing something there.

=SUM(Num(Aggr(MAX(TestDate), ClientAccountName, ExternalID), CholRatioTotal)/
(Num(Aggr(MAX(TestDate), ClientAccountName, ExternalID), CholRatioHDL)))

(Note: Max(TestDate) is there because I want only the most current testdate for each Client/ID combination.  Trying to slice and dice the data without generic loads.)

Thanks! -Steve

[#2]

Is there a reason your data model is in that specific format?  I would recommend a simple join in order to get rid of your problem all together.  If that isn't possible for some reason, you can use Aggr().  See if the attached file is what you have in mind.

[nagaiank]

One way of accomplishing what you want is in the attached application.

[stevelord]

I broke some testname values out of a testname field then made fields out of them and I sensed they were considered to be on different rows/records still.  I tried Aggr() in a way that should have worked, but mgiht be missing something.  I had a thought about making a little temporary table for each one to separate them, then rejoin with a leftjoin on a concatenated user/company/date field to see if that puts them on the same row.

Are you able to paste the script into the body of the email?  I am still on QV personal and can't open other people's files.  (Am a temp presently. )

[stevelord]

Are you able to paste the script into the body of the email?  I am still on QV personal and can't open other people's files.  (Am a temp presently. )