# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## Match() and field with text and numbers

Hi guys,

I have a field in my data that has text and number values:

Group
22
AB2
250
FD

It looks like some of the numbers are not interpret correctly, so that my match function does not work as expected:

Using IF(MATCH(Group,'22','AB2','250','FD'), 'East') AS Location returns the following:

LocationGroup
East22
-22
EastAB2
East250
-250
EastFD

Then I tried IF(MATCH(NUM(Group),'22','AB2','250','FD'), 'East') AS Location. This works for the numbers but then the text fields get lost:

LocationGroup
East22
-AB2
East250
-FD

IF(MATCH(TEXT(Group),'22','AB2','250','FD'), 'East') AS Location returns the same as the first example:

LocationGroup
East22
-22
EastAB2
East250
-250
EastFD

As you can expect, this is the desired outcome:

LocationGroup
East22
EastAB2
East250
EastFD

1 Solution

Accepted Solutions

## Re: Match() and field with text and numbers

You could try it with something like this:

IF(MATCH(alt(num(Group), text(Group)),'22','AB2','250','FD'), 'East') AS Location

You might need to extend this logic to some cleaning and/or converting-approaches like trim(), keepchar() or num#() or maybe even upper(), lower() to ensure that the field-values are matching your list.

- Marcus

8 Replies

## Re: Match() and field with text and numbers

HI,

It seems your is statement is wrong.

Try below.

IF(MATCH(Group,'22','AB2','250','FD'), 'East') AS Location

Regards,

Kaushik Solanki

New Contributor III

## Re: Match() and field with text and numbers

Yeah, sorry about the typo, I just corrected the first post.

New Contributor III

## Re: Match() and field with text and numbers

HI,

It is working perfectly on my machine.

Can you share your application. Also let us know what version you are using.

Regards,

Kaushik Solanki

MVP

## Re: Match() and field with text and numbers

Agreed - it works OK

If(Match(Group,'22','AB2','250','FD'), 'East') AS Location

Inline

[

Group

22

AB2

250

FD

ZZ

];

New Contributor III

## Re: Match() and field with text and numbers

Hi guys! Thank you for looking into this!

Yes, this sample data works. Also when I export the actual data to excel and load it to a new app it works.

The formula is correct, but it looks like the problem is in the way this field is formatted in the database so that Qlik cannot interpret the values correctly. That's why I was looking for a way to format the string in Qlik. Apparently NUM() and TEXT() are not the right options, maybe there is something else I can use?

## Re: Match() and field with text and numbers

You could try it with something like this:

IF(MATCH(alt(num(Group), text(Group)),'22','AB2','250','FD'), 'East') AS Location

You might need to extend this logic to some cleaning and/or converting-approaches like trim(), keepchar() or num#() or maybe even upper(), lower() to ensure that the field-values are matching your list.

- Marcus

New Contributor III

Thank you!!!