Hidden characters in data

[Lauri]

Does my data contain hidden characters? I have a field that contains both numbers and strings. When I try to filter on a specific numeric value (99396) in set analysis, it returns no results. When I filter on '*99396*' it returns results. Yet the length of the field is always 5. Does Qlik store hidden characters? Do they have zero length?

Here's what my output looks like:

If it's hard to read, here is the first column's text:

count({<Procedure_Code={99396}>}ProcedureDate)

The other columns' filters are, from left to right:

Procedure_Code={'99396'}

Procedure_Code={'*99396'}

Procedure_Code={'99396*'}

Procedure_Code={'*99396*'}

It's strange that the 3 combinations of wild cards have the same result. It suggests something other than a hidden character at the start or end.

And the length is 5:

Thanks for any ideas.

[Chanty4u · Accepted solution]

Hi 

Try this  in script.

LOAD

    Text(Trim(PurgeChar(Procedure_Code, Chr(160)))) as Procedure_Code

FROM ...

Or test like in set analysis try this 

count({<Procedure_Code={"*99396*"}>} ProcedureDate)

[Lauri]

Thanks, @Chanty4u - that definitely helps. I did this in the load:

If(IsNum(Procedure_Code1), Floor(Procedure_Code1), Text(Trim(Procedure_Code1))) as Procedure_Code

(I wanted to see if purging chr(160) was necessary or not. But it is a good idea to include it.)

I now can use the number and the 5-character string in set analysis. 

It's curious to me that the last search -- '*99396*' -- in single quotes doesn't work. I take that to mean Qlik is looking for a string with some character before or after the literal, whereas double quotes tells it to do a broader search including 99396 with nothing before or after.