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

Matching up results from multiple calculated fields

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

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

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

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
• 4. Re: Matching up results from multiple calculated fields

This worked.  Thank you!!