# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## 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

Tags (2)
1 Solution

Accepted Solutions
New Contributor III

## 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.

20 Replies
New Contributor III

## 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.

New Contributor

## 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.

New Contributor III

## 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.

MVP

## Re: If statement. Need help

In the front-end:

Chart table:

Dim: [Patient]

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

New Contributor

## Re: If statement. Need help

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

Honored Contributor II

## Re: If statement. Need help

maybe this in your chart dimension:

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

New Contributor

## 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.

Honored Contributor II

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

New Contributor

## 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.