Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

steve_br
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

Can somebody please help me here? Thanks!

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

steve_br
New Contributor III

Re: Match() and field with text and numbers

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

steve_br
New Contributor III

Re: Match() and field with text and numbers

Can anybody please suggest something?

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
MVP

Re: Match() and field with text and numbers

Agreed - it works OK

LOAD *,

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

Inline

[

Group

     22

     AB2

     250

     FD

     ZZ

];

Capture.PNG

steve_br
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

steve_br
New Contributor III

Re: Match() and field with text and numbers

Thank you!!!

Community Browser