Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
akuttler
Creator
Creator

Caret Symbol in wild match

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.

10 Replies
MarioCenteno
Creator III
Creator III

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

akuttler
Creator
Creator
Author

Same results, ignores the string all together.

petter
Partner
Partner

Actually the caret symbol have a special meaning although the documentation on WildMatch doesn't mention this - however you can find the information here:

Screenshot from 2018-08-25 10-26-16.png

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Selections/SelectionsToolbar/searc...

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 ˄*' )

karthiksrqv
Partner
Partner

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)

akuttler
Creator
Creator
Author

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.

ArnadoSandoval
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
karthiksrqv
Partner
Partner

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)

avkeep01
Partner
Partner

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

avkeep01
Partner
Partner

Could also be

CHR(94) = ^

CHR(708) = ˄

CHR(710) = ˆ