If statement. Need help

[Anonymous]

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

[john_obrien]

Try this in the load script to add a field:
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.

[Anonymous]

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.

[john_obrien · Accepted solution]

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:

Load

     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 

Left Join (tempVisits) Load Distinct

     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:

Load

     *,

     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.

[tresesco]

In the front-end:

Chart table:

Dim: [Patient]

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

[Anonymous]

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

[agigliotti]

maybe this in your chart dimension:

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

[Anonymous]

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.

[agigliotti]

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] ) ) 

[Anonymous]

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