Use CONCAT with "other" string functions.

[alexdataiq]

Hi everybody, I'm facing some issues here:

Is it possible to use the Match function with the result of a Concat or Index function over a field? Doesn't seem to be working for me, Match always return 0. Maybe I'm missing something here?

I've a attached a very simple example.

Regards.

[sunny_talwar · Accepted solution]

Try this:

=Match('X1', $(=Chr (39) & Concat(Sheet, Chr(39) & ',' & Chr(39)) & Chr(39)))

or this:

=SubStringCount(Chr (39) & Concat(Sheet, Chr(39) & ',' & Chr(39)) & Chr(39), 'X1')

[swuehl]

Or maybe just

=Max(Match(Sheet,'X1'))

though it's not using Concat()..

[alexdataiq]

Awesome! You're the man Sunny!

If I don't use the dollar expansion what string am I inputting in the second parameter of Match? Chr(39) gets recognized (colored in blue) in the editor so I assumed it was evaluating the string.

Regards

[alexdataiq]

Yeah, that would also work out. Now that I see it, it's so obvious!

Regards

[swuehl]

If I don't use the dollar expansion what string am I inputting in the second parameter of Match? Chr(39) gets recognized (colored in blue) in the editor so I assumed it was evaluating the string.

I think the expression editor syntax checker is misleading here. It doesn't seem to evaluate the expression at all.

I believe the Match() function expects just literal strings, comma separated as arguments to match the first argument (which could be an expression or field) against.

Besides this, if you like the SubstringCount() function, you don't need the fancy formatting here:

=SubStringCount( Concat(Sheet), 'X1')

or maybe using a delimiter if you have also values like X and 1 in your Sheet field:

=SubStringCount( Concat(Sheet,'|'), 'X1')

or if you want to use Index():

=Index( Concat(Sheet), 'X1')

this might return values > 1 if 'X1' has been found, but Qlik will consider all values >0 as TRUE in a conditional expression (I assume that's where you need this expression)

Regards,

Stefan

[alexdataiq]

Thank you very much Stefan. I'm sure this will come in handy in the future!

Regards