4 Replies Latest reply on Oct 8, 2018 12:35 PM by Kayla Luckett

    Matching up results from multiple calculated fields

    Kayla Luckett

      I'm working with a table that includes patient account numbers, their diagnosis codes and the sequence of these codes. 

       

      Patient 1, Sequence 1, Diagnosis Code

      Patient 1, Sequence 2, Diagnosis Code

      Patient 2, Sequence 1, Diagnosis Code

      Patient 3, Sequence 1, Diagnosis Code

      etc.

       

      I have a calculated field that says if the sequence = 1 and the diagnosis code matches one of these codes I have listed then return the patient account number.  Works great.

       

      Then I have another calculated field that says if the sequence = 1 and the diagnosis code matches one of these other codes, return the patient account number.  Again, works.

       

      Then a 3rd that says if the sequence >1 and the diagnosis code is one in this list, return the patient number.  And this works as well.

       

      However, then I need to say if this patient account is in the first calculated field OR the 2nd AND 3rd fields, return patient account.  And this doesn't work.  I've tried it several ways.  Any ideas?

        • 1. Re: Matching up results from multiple calculated fields
          Shinichiro Murakami

          HI Kayla,

           

          The  data variation is not clear.

          Could you attach sufficient number of mocked-up sample data as twbx or excel.

           

          Thanks,

          Shin

          • 2. Re: Matching up results from multiple calculated fields
            Michael Ye

            What means "patient account is in the first calculated field OR the 2nd AND 3rd fields"? Here you did not mention and introduce calculated fileds in above words.

            Can you explain it?

             

            Michael Ye

            • 3. Re: Matching up results from multiple calculated fields
              swaroop.gantela

              Kayla,

               

              I may not have quite gotten the gist, nor quite achieved the goal,

              but maybe these can give ideas.

               

              I think the issue at hand is that you might need to aggregate

              each your calculated fields over all rows for a patient account.

              Since each row is a separate sequence and diagnosis, it won't know

              about what is happening in another row unless you fix the value of each

              conditional check to each patient row.

               

              Using your patient account method, I:

              -performed each check (if the check returned a PtAcct, then it wasn't null),

              -took the max of that check across the PtAcct,

              -and fixed that to each row in the account:

               

              Individual check [Check1]:

              IF [Sequence]=1 AND [Diagnosis Code]="ICD01" THEN [Patient Acct] END

               

              [CheckOverall]:

              IF NOT ( ISNULL ( { FIXED [Patient Acct] : MAX ( [Check1] ) } ) )

                  OR (

                      NOT ( ISNULL ( { FIXED [Patient Acct] : MAX ( [Check2] ) } ) )

                      AND

                      NOT ( ISNULL ( { FIXED [Patient Acct] : MAX( [Check3] ) } ) )

                      )

              THEN [Patient Acct]

              END

               

              I am not sure if you are using the PtAcct resulting from each check

              for other things. If not, you can simplify it a bit by using 1 or 0 flags

              which may be computationally faster than string checking:

               

              [NumCheck1]:

              IF [Sequence]=1 AND [Diagnosis Code]="ICD01" THEN 1 ELSE 0 END

               

              [NumCheckOverall]:

              IF { FIXED [Patient Acct] : MAX ( [NumCheck1] ) } = 1

                  OR (

                      { FIXED [Patient Acct] : MAX ( [NumCheck2] ) } = 1

                      AND

                      { FIXED [Patient Acct] : MAX ( [NumCheck3] ) } = 1

                  )

              THEN [Patient Acct]

              END

               

              This can also be with Table Calculations.

               

              Please see workbook v10.3 attached in the Forum Thread.

               

               

              If these are not meeting your goal, please adjust the workbook

              and attached datasource to more closely match yours.

              1 of 1 people found this helpful