20 Replies Latest reply: Feb 5, 2018 9:08 AM by Jay Shahi

# If statement. Need help

I work for a health system. We are trying to calculate new patient visits vs follow up visits.

The only variable we have to measure difference are CPT codes that are created each time a person comes in whether for a new visit or follow up. Unfortunately CPT codes for new patient visits are exclusive for new patients but follow up CPT codes could also go into new visits.

We tried this function.

If (match (CPT code '1111','2222' etc), 'new')

if not( match (CPT code '1111','2222' etc), 'follow up')

I am not big on coding so please excuse if this looks remedial.

The main goal is to find a statement that will

1. mark a patient visit NEW if the visit contains even 1 CPT code that is in the new list.

2. Will only mark it as a follow up visit if there are no new CPT codes within the visit.

Please let me know if you need more clarification. Any help would be very much appreciated.

Thanks

• ###### Re: If statement. Need help

If( MixMatch([CPT code] , '1111', '2222', '3333') > 0 , 'new', 'follow up') as [Visit Type]

Or this as a calculated dimension in a chart:

If( MixMatch([CPT code] , '1111', '2222', '3333') > 0 , 'new', 'follow up')

The MixMatch function does not care about the case of the strings being matched.

• ###### Re: If statement. Need help

Thanks for the reply John. Unfortuantely, it did had the same result. It was counting some of the new as follow up because the follow up CPT codes were present in the 'New' visits as well.

• ###### Re: If statement. Need help

Well, It looks like  your question is not as simple as I first thought.  Not a problem - some load script work can solve this for you.  I think it would be best to calculate a field on the table for patient visits.  If you have a table with fields for  Visit Identifier and CPT Code (only one code per row), then this should work.  (There may be better ways to do this depending on your data and data model.)

// Make temporary table with the visit data

tempVists:

VisitID,

[CPT code],

field3, field4 ...

From ...;

// Add a field for all the new visits - those that have at least one CPT code for a new visit

VisitID

'new' as VType

Resident tempVisits

Where

MixMatch([CPT code] , '1111', '2222', '3333') > 0 ;

// Now all the new patient visits have a VType of 'new' and the others have null

// Create a new table that changes the nulls to 'follow-up'

Visits:

*,

IF( ISNull(VType), 'follow-up', VType) as [Visit Type]

Resident tempVisits;

Drop Table tempVisits;  // don't need this table anymore

Drop Field VType; // don't need this field anymore

I hope that example fits your situation.

• ###### Re: If statement. Need help

Thanks for the input John, I just emailed our BI guys to take a look at it. I'll let you know if this it! Much appreciated.

• ###### Re: If statement. Need help

In the front-end:

Chart table:

Dim: [Patient]

Exp: If(Max(match ([CPT code] '1111','2222')), 'New', 'Follow up')

• ###### Re: If statement. Need help

Tresesco, its giving me an error. It wont execute.

• ###### Re: If statement. Need help

maybe this in your chart dimension:

=If( Match([CPT code], '1111', '2222'), 'New', 'Follow up')

• ###### Re: If statement. Need help

We did that, but its giving us some of the new visits as follow ups as well because the both new and other CPT codes can exist in one visit.

• ###### Re: If statement. Need help

to count the new visits:

Count( Aggr( if( count( DISTINCT [CPT code] ) = 1, [CPT code] ), [CPT code] ) )

to count the follow-up visits:

Count( Aggr( if( count( DISTINCT [CPT code] ) > 1, [CPT code] ), [CPT code] ) )

• ###### Re: If statement. Need help

The statement runs ok in the script window, but tells me its an invalid dimension when it loads in the pivot.

• ###### Re: If statement. Need help

how is your pivot table (row,column,measure)?

what i wrote before is a measure not a dimension.

what's your expected result with pivot table ?

• ###### Re: If statement. Need help

We're trying to run it as a calculated dimension.

• ###### Re: If statement. Need help

Hey Jay, let me sse if I understand your requirements. Just try to create a code like this:

- First, create a tmp table and select patients with group by who have 1 an 2 visits;

- Create a secondary table to store who have1 and mark with if statement;

- Create a thirty table who have 2 visits with the second if statement;

- Then, create a main table, loading this two tables residents creating;

• ###### Re: If statement. Need help

We're trying to run it as a calculated dimension.

So the requirement is this.

If a patient has a NEW CPT code within their visit, then we have to count them as NEW, even if they have CPT codes shown in the above pic as 'other"

The problem is that the match function is returning is correctly returning the 'new' CPT codes back as new patients, but its also counting the new as other because some  VISITS contains both CPT codes.

• ###### Re: If statement. Need help

it's still unclear.

you should provide a sample app with mock data to try to help you.

• ###### Re: If statement. Need help

you could try to do:

as pivot row:

=Aggr( if( count( DISTINCT [CPT code] ) = 1, 'New',

if( count( DISTINCT [CPT code] ) > 1, 'Follow up' ) ), [CPT code] )

as pivot measure:

count( [CPT code] )

• ###### Re: If statement. Need help

I gave it a try, was not successful. We are handing it off to the BI team to research it further. I think they will try to find a more granular association that is not dependent on CPT codes. Thanks for your help Andrea!

• ###### Re: If statement. Need help

Hello,

try my solution based on calculated dimension , i implement an example from your explication and it done me this result

from cpt 111 until 116 is new and if else follow

my formula

=if(cpt='111','new',if(cpt='112','new',if(cpt='113','new',if(cpt='114','new',if(cpt='115','new',if(cpt='116','new','follow'))))))

• ###### Re: If statement. Need help

Sorry, did not work. We are giving it to the BI team. Hopefully they can make it work.

• ###### Re: If statement. Need help

if(Count(match(CPT code, List) )= 1,New,Follow up)