Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Location | Group |
---|---|
East | 22 |
- | 22 |
East | AB2 |
East | 250 |
- | 250 |
East | FD |
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:
Location | Group |
---|---|
East | 22 |
- | AB2 |
East | 250 |
- | FD |
IF(MATCH(TEXT(Group),'22','AB2','250','FD'), 'East') AS Location returns the same as the first example:
Location | Group |
---|---|
East | 22 |
- | 22 |
East | AB2 |
East | 250 |
- | 250 |
East | FD |
As you can expect, this is the desired outcome:
Location | Group |
---|---|
East | 22 |
East | AB2 |
East | 250 |
East | FD |
Can somebody please help me here? Thanks!
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
HI,
It seems your is statement is wrong.
Try below.
IF(MATCH(Group,'22','AB2','250','FD'), 'East') AS Location
Regards,
Kaushik Solanki
Yeah, sorry about the typo, I just corrected the first post.
Can anybody please suggest something?
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
Agreed - it works OK
LOAD *,
If(Match(Group,'22','AB2','250','FD'), 'East') AS Location
Inline
[
Group
22
AB2
250
FD
ZZ
];
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?
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
Thank you!!!