Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to perform exact match on concatenated string

Hello,

I have a scenario like the following

Table:

TESTING123

TESTING

Concatenated String:

TESTING123|TESTING

When I do a match in table for TESTING, I'm getting 2 items returned.  How can I make it so that my match ensures that the whole string matches and not something like a "contains"?  I can't really use match() because I'm actually looking in a concatenated string.  Also, I'll never know at what position my string exists.  Currently, I'm doing an Index(), but that's returning both strings, not just the 1 that I want.  Hope that makes sense.  Thanks!

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

Maybe a little trick can do what you want

=index(Concat(F1&'|'),'Testing'&'|')  only return 12

Table:

LOAD * INLINE [

    F1

    Testing

    Test

    Testing123

];

View solution in original post

15 Replies
Clever_Anjos
Employee
Employee

Are you using match function? a listbox?

Anonymous
Not applicable
Author

Looks familiar.  In situations like this, I avoid values that can be substrings of the other values.  Replace 'TESTING' with something like 'TESTING_'

Not applicable
Author

I wish I could, but I can't. These values are coming from a database and we can't change the database beacuse of the impact that would have.

Sent from my mobile device

Anonymous
Not applicable
Author

Sorry about that.

Still, there are always solutions.  In this particular case:

LOAD

...

if(Field='TESTING', 'TESTING_', Field) as Field

;

SELECT...

Or maybe add a character at the end of each value, it is more reliable, reduces chances to have values which are substrings of the other values:

Field & '_' as Field

Regards,

Michael

tresesco
MVP
MVP

Though not very clear about the exact requirement, you might want to have a look at FindOneOf() function which gives you the position of the matched string for n'th occurance.

Not applicable
Author

But then won't my listboxes have the additional character as well for those selections? That's not an acceptable solution if so.

Sent from my mobile device

Anonymous
Not applicable
Author

This can be done if you keep both fields, the original for the list boxes, and a copy for concatenation:

LOAD

...

Field,

Field & '_' as Field2

...

(I know it is also not perfect)

Clever_Anjos
Employee
Employee

Maybe a little trick can do what you want

=index(Concat(F1&'|'),'Testing'&'|')  only return 12

Table:

LOAD * INLINE [

    F1

    Testing

    Test

    Testing123

];

Anonymous
Not applicable
Author

Instead of adding "suffix" in the script, you're adding it right there in expression.

Looks reasonable.