Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
steve_br
Creator
Creator

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
marcus_sommer

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

View solution in original post

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

It seems your is statement is wrong.

Try below.

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


Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
steve_br
Creator
Creator
Author

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

steve_br
Creator
Creator
Author

Can anybody please suggest something?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jonathandienst
Partner - Champion III
Partner - Champion III

Agreed - it works OK

LOAD *,

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

Inline

[

Group

     22

     AB2

     250

     FD

     ZZ

];

Capture.PNG

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
steve_br
Creator
Creator
Author

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?

marcus_sommer

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
Creator
Creator
Author

Thank you!!!