topic Re: Use CONCAT with "other" string functions. in QlikView

Use CONCAT with "other" string functions.

alexdataiq — Wed, 18 May 2016 22:42:50 GMT

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.

Re: Use CONCAT with "other" string functions.

sunny_talwar — Wed, 18 May 2016 22:47:48 GMT

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

Re: Use CONCAT with "other" string functions.

swuehl — Wed, 18 May 2016 22:53:34 GMT

Or maybe just

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

though it's not using Concat()..

Re: Use CONCAT with "other" string functions.

alexdataiq — Wed, 18 May 2016 22:55:29 GMT

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

Re: Use CONCAT with "other" string functions.

alexdataiq — Wed, 18 May 2016 23:00:50 GMT

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

Regards

Re: Use CONCAT with "other" string functions.

swuehl — Wed, 18 May 2016 23:19:49 GMT

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

Re: Use CONCAT with "other" string functions.

alexdataiq — Thu, 19 May 2016 14:33:18 GMT

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

Regards