topic Re: Match problems in QlikView

Match problems

Anonymous — Thu, 18 May 2017 14:33:47 GMT

I have a one-record table with the following column:

AppAbbrevs

which is a string that contains the following:

'Field1,Field2,Field3'

When I try MATCH('Field3', ',', AppAbbrevs), it returns 0. It also returns 0 if I surround each value with single quotes.

What am I doing wrong?

Re: Match problems

tresB — Thu, 18 May 2017 14:36:43 GMT

May be like this?

MATCH(AppAbbrevs, 'Field3' )

Re: Match problems

Anonymous — Thu, 18 May 2017 14:44:00 GMT

Hi John,

Are you saying that your column has one record, and that record is a string 'Field1,Field2,Field3'?

If so I don't believe the Match() function would work. You should use WildMatch() as that will search through a string.

Match is used to look for a specific string within multiple records, so for example:

Customer is the column

record 1: 'A'

record 2: 'B'

record 3: 'C'

You would use Match to find customer B for example.

Where as you are wanting to grab part of a big string from one record.

For more on the Match() function, look here: match function in qlikview

And for more on the WildMatch() function, look here: wildmatch - script and chart function ‒ Qlik Sense

The function you would require is: WildMatch(AppAbbrevs, '*Field3*')

Hope this helps, and if you are satisfied with the answer please mark it as correct. If you need further assistance I can help.

Re: Match problems

Anonymous — Thu, 18 May 2017 15:05:14 GMT

I want the index of the substring within the larger string. Am I using the wrong function?

Re: Match problems

tresB — Thu, 18 May 2017 15:07:46 GMT

Index()?

Re: Match problems

Anonymous — Thu, 18 May 2017 15:11:48 GMT

Hi John, you are looking for this then.

Index(AppAbbrevs, 'Field3')

Re: Match problems

Anonymous — Thu, 18 May 2017 15:13:35 GMT

I tried that, and it gave me the character index, I want the item index in the delimited string. In the example I provided, I want "3".

Re: Match problems

Anil_Babu_Samineni — Thu, 18 May 2017 15:17:49 GMT

Can you provide sample data and expected result?

Are you talking about this?

Right(FieldName,1)

Re: Match problems

Anonymous — Thu, 18 May 2017 15:25:37 GMT

I want the INDEX of the substring within the larger delimited string.

In the example I provided, I'm looking for 'Field3', and expecting an index of 3 because 'Field3' is the third delimited item in the string.

Re: Match problems

Anil_Babu_Samineni — Thu, 18 May 2017 15:30:27 GMT

Still confusing, May be this?

If(SunStringCount(FieldName, 'Field3'), '3')

Re: Match problems

Anonymous — Thu, 18 May 2017 15:31:26 GMT

Ok John I have the solution!

Stick this in your data load editor

Strings:

LOAD

RowNo() as row,

Subfield(AppAbbrevs,',') as SingleStrings

Resident YOUR_TABLE_NAME_HERE_JOHN;

Then in your visualizations do:

if(SingleStrings = 'Field3', row)

That will do it

Re: Match problems

tresB — Thu, 18 May 2017 15:40:17 GMT

A front-end solution could be like:

=SubStringCount( Left( 'Field1,Field2,Field3', Index('Field1,Field2,Field3' , 'Field3')), ',')+1

Orange - Your field value string

Blue - Search string

Pink - Delimiter

Re: Match problems

Anonymous — Thu, 18 May 2017 15:55:56 GMT

Wow. That gives me the result I want, but it's more complicated than it should be...

It's this kind of thing that completely negates what makes qlikview a decent tool.

In any case, many thanks.