Import SQL Server varchar field as text containing exact value
I am new to QlikView and am struggling somewhat with what should be really simple tasks. I have a SQL Server database with a varchar field. This field contains both numeric and non-numeric data. The data should be numeric, so I am writing an exception report to detail where the field contains any non-numeric characters (which are preventing the value being cast to numeric in SQL). So, I might have values such as '£1000', '1,000', '10%', '01/01/2017', '1 abc', '1000.25', or '1000'. In SQL Server, only the last 2 values can be cast, and all of the others should be included in the report.
The problem I have is that Qlik seems to recognise all of those as numbers, and I am just getting the numeric value loaded into my file. So, '1,000' is displayed as 1000 (without the comma). I cannot format the numbers to include the missing characters, as not all of the errors are due to the same character.
I need to be able to display the exact text as stored in SQL Server so that the report user will understand why the record appears on the report. I have tried to find a text function in the script editor to force this at load stage, and I get a message saying the built in text function does not exist (this is the solution I find in most forums, but does not help me unfortunately). I cannot use the function in the chart expressions, as this only converts the loaded value to text, so I am still missing the commas and £ signs etc. So, I am hoping someone out there can assist. I am using QlikView 11 and SQL Server 2012.