Is there any reason why string functions come out null in a data model?
I have a field in my data called ID which is supposed to be the format - A111111, so 1 character, followed by 6 numbers.
There are a few values where this ID is preceded by some garbage text, so to remove this, I use the statement:
If(isnum(right(ID,6)) and istext(left(right(ID,7),1), ID,'N/A') as ID
This statement works perfectly on the front end. When I select any ID, I can see the proper format displayed. But for some strange reason, this statement only works for a few values in my ID list on the back end. Even in the case of multiple rows with the same ID, only 1 instance displays the correct ID and all the other are nulls.
I tried to debug this and pulled out the actual characters from my Left and Right statements and these are showing up as null as well even though the ID field is 100% populated.
I've tried trimming and using upper on my values, but nothing seems to work. Is there any reason why basic string functions turn up as null when the field is populated?
Re: Is there any reason why string functions come out null in a data model?
You need to check which content your field really had. Quite useful for it is to copy (some parts) into an editor like notepad ++ or to check them on char-level within qlikview, for example with something like this:
load Field, len(Field) as FieldLength, ord(mid(Field, iterno(), 1)) as Char, iterno() as IterNo, rowno() as RowNo
from YourSource while iterno() <= len(Field);
I wouldn't be surprised if there are any not visible special chars and not each of them are removeable with trim(). Most often will be: