Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have the following dimension:
=if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-24^*'),'Capitation' ,
if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*OA-193*'),'Appeal Denial',
if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-242*'),'Provider')))
However, the Capitaion match statement does not work when I add the "^" symbol at the end.
Is there a simple way to remedy this?
Thanks.
Try
=if([LINE_ITEM_ADJUSTMENTS] LIKE '*CO-24 ^*' , 'Capitation',
if(LINE_ITEM_ADJUSTMENTS LIKE '*OA-193*' ,'Appeal Denial',
if(LINE_ITEM_ADJUSTMENTS LIKE '*CO-242*' ,'Provider','Other')))
Same results, ignores the string all together.
Actually the caret symbol have a special meaning although the documentation on WildMatch doesn't mention this - however you can find the information here:
The meaning "beginning of a word within field value":
WildMatch( 'ABC','^abc') will give a match.
So will:
WildMatch('ABC DEF NNN','*^DEF *')
A workaround would be to do this:
WildMatch( Replace( 'asdfkljsfdlkj ABC^asdflkjsadflk' , '^' , '©' ) , '*ABC©*' )
Replace the caret with any character that you know doesn't exist in your field and use that character to specify the pattern. The replacement character could also be a non printing character or any character in the UTF-8 code set.
You could even use a non-standard caret symbol as a temporary replacement for matching (if you risk it):
WildMatch( Replace( 'xyzabc CO-24 ^nnnnn' , '^' , '˄' ) , '*CO-24 ˄*' )
or rather in your case:
WildMatch( Replace( LINE_ITEM_ADJUSTMENTS, '^' , '˄' ) , '*CO-24 ˄*' )
Is there a reason you have the caret symbol in that expression? Am assuming it is part of the data value.
perhaps
instead of just if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-24*'))
if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-24*') and substringcount(LINE_ITEM_ADJUSTMENTS,chr(94)>0)
because it will pick up other code like "CO-242"...all the codes are separated with "^" in the strings so it signifies the end of the code.
Hi Ashley,
If I understood you issue, you are dealing with the folloing column: [LINE_ITEM_ADJUSTMENTS], it contains line adjustment codes separated by carrots (^), which is fine; the information coming in this variable could be like:
CO-24^CO-242^OA-193
The sample contains the codes without leading-trailing carrots and the carrot itself is a wildcard in the wildmatch function.
It is better to get-rid of the carrots, I will suggest the pipe '|' character (it is up to you based on the data), the same process replacing the carrots with pipes should insert the leading and trailing pipes, something like this:
'|' & Replace(LINE_ITEM_ADJUSTMENTS, '^', '|') & '|' as workText
The output of the previous function transform your line item adjustments into
|CO-24|CO-242|OA-193|
After replacing carrots with pipes, your WildMatch will resolve these search.
WildMatch( [LINE_ITEM_ADJUSTMENTS], '*|CO-24|*' )
WildMatch( [LINE_ITEM_ADJUSTMENTS], '*|CO-242|*' )
WildMatch( [LINE_ITEM_ADJUSTMENTS], '*|OA-193|*' )
Now, this approach will fail when the LINE_ITEM_ADJUSTMENTS contains the codes CO-24 and CO-242; but if you implement the PICK function suggested in your other thread, you may not have any issue at all !!!
Let us know if this suggestion is in the right track, and if your data is unlikely to fail with the condition shown:
SIDE NOTE:
The meaning of the carrot in wild search is 'the field is starting with the search value'
Hope this helps,
How about
=SubStringCount('CO-24552^CO-24^ABC-123','CO-24^')
just for this one string? The first option should be as as Arnaldo has suggested (to replace ^ with some other delimiter that is not a regular expression character)
Hi Ashley Kuttler,
The ^ is a symbol with an function so it won't be seen as text. The character can alse written like CHR(708).
Try the following:
=if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-24'&CHR(708)&'*'),'Capitation' ,
if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*OA-193*'),'Appeal Denial',
if(wildmatch(LINE_ITEM_ADJUSTMENTS, '*CO-242*'),'Provider')))
Could also be
CHR(94) = ^
CHR(708) = ˄
CHR(710) = ˆ