

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use CONCAT with "other" string functions.
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or maybe just
=Max(Match(Sheet,'X1'))
though it's not using Concat()..


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yeah, that would also work out. Now that I see it, it's so obvious!
Regards


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much Stefan. I'm sure this will come in handy in the future!
Regards
