Is there any reason why string functions come out null in a data model?

[nikita42]

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?

[vishsaggi]

Can you show some sample ID values or data ?

[m_woolf]

Why not load your ID field as is and then load a new field with the string expressions, so you can compare the ID with the results of the expression when the expression is NULL?

[nikita42]

That's what I've done. I have my ID field, and an ID proper field, which is populated for some rows, but blank in most even though the ID is populated on every row.

I also have a column for Right(ID,1) and Left(Right(ID,7),1) which are both also blank for most rows.

[m_woolf]

We need to see a sample app with a few records that illustrate the problem.

[nikita42]

Unfortunately, I can;t do that because of data sensitivity issues. I can't replicate the error either, because it's definitely a data/format issue specific to my data set.

I was just wondering whether there are some common text/string errors that anyone knew of that could be causing this issue.

[marcus_sommer]

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:

FieldCheck:

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:

keepchar(Field, '0123456789abcdefghijklmnopqrstuwvxyzABCDEFGHIJKLMNOPQRSTUWVXYZ')

which worked like a while-listing the easiest way to solve the challenge.

- Marcus