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.
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
field3, field4 ...
// 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
'new' as VType
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'
IF( ISNull(VType), 'follow-up', VType) as [Visit Type]
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.
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;
I guess that may help you.
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.
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