topic Re: How to perform exact match on concatenated string in QlikView

How to perform exact match on concatenated string

Wed, 23 Oct 2013 18:21:33 GMT

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!

Re: How to perform exact match on concatenated string

Clever_Anjos — Wed, 23 Oct 2013 18:23:51 GMT

Are you using match function? a listbox?

Re: How to perform exact match on concatenated string

Anonymous — Wed, 23 Oct 2013 18:41:12 GMT

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

Re: How to perform exact match on concatenated string

Wed, 23 Oct 2013 18:46:19 GMT

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

Re: How to perform exact match on concatenated string

Anonymous — Wed, 23 Oct 2013 18:57:02 GMT

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

Re: How to perform exact match on concatenated string

tresB — Wed, 23 Oct 2013 18:58:04 GMT

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.

Re: How to perform exact match on concatenated string

Wed, 23 Oct 2013 19:03:07 GMT

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

Re: How to perform exact match on concatenated string

Anonymous — Wed, 23 Oct 2013 19:06:43 GMT

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)

Re: How to perform exact match on concatenated string

Clever_Anjos — Wed, 23 Oct 2013 19:38:53 GMT

Maybe a little trick can do what you want

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

Table:
LOAD * INLINE [
    F1
    Testing
    Test
    Testing123
];

Re: How to perform exact match on concatenated string

Anonymous — Wed, 23 Oct 2013 19:58:48 GMT

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

Looks reasonable.

Re: How to perform exact match on concatenated string

Clever_Anjos — Wed, 23 Oct 2013 20:04:37 GMT

Michael Solomovich it´s a great honor receive a reply from you . You´re one of my "QHeroes"

Re: How to perform exact match on concatenated string

Anonymous — Wed, 23 Oct 2013 20:07:30 GMT

Re: How to perform exact match on concatenated string

Thu, 24 Oct 2013 12:18:53 GMT

OK this was definitely one of those "why didn't I think of that" scenarios! I'm already doing the Index and the Concat so I'll just add my delimiter to the end of my search string. And I'll say it again - why didn't I think of that?!

Thanks as always!

Re: How to perform exact match on concatenated string

Thu, 24 Oct 2013 12:38:51 GMT

I've noticed one fallout on this... what if I've only made 1 selection? So the concat I'm doing is on the list of selections made from a listbox. If there's only 1 selection, there will be no delimiter added to the concat and therefore I cannot search on the string plus the delimiter. Also, no matter the number of selections, the last one in the list is not being found because it also has no delimiter trailing.

Re: How to perform exact match on concatenated string

Clever_Anjos — Thu, 24 Oct 2013 12:50:45 GMT

Please note that I didn´t use '|' as a separator to Concat, but concatenated to field, so the '|' is always there

Re: How to perform exact match on concatenated string

Thu, 24 Oct 2013 12:55:31 GMT

oohhhh!! I see that now, thanks for pointing out the difference!