topic Re: Import SQL Server varchar field as text containing exact value in QlikView

Import SQL Server varchar field as text containing exact value

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

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.

Many thanks in advance.

Re: Import SQL Server varchar field as text containing exact value

devarasu07 — Mon, 10 Jul 2017 17:58:38 GMT

Hi,

you can try like below

Text(FieldName) as FieldName

or if you wanted to import exact numeric value sql/other source into qlik then like below

num#(FieldName, '#','.',',') as numFieldName

Thanks

Re: Import SQL Server varchar field as text containing exact value

Mon, 10 Jul 2017 18:41:48 GMT

Hi, many thanks for your response. However, as per the query, the text function is not working in my script. I get an error message advising that it is not a built in function.

Re: Import SQL Server varchar field as text containing exact value

Siva_Sankar — Tue, 11 Jul 2017 02:49:35 GMT

Hi Dawn,

Try like this.. in the load statement use grave accents i.e enclose the field name inside the grave accents.

grave accent is something like single quote but not exactly single quote in your keyboard.

The grave accent (`) is under the tilde (~) key on your keyboard

This will help to retain the exact format as in sql server into the qlikview.

Load

`ID`,

`Name`;

SQL select ID

Name

from dbo.xyz;

-Siva

Re: Import SQL Server varchar field as text containing exact value

jonathandienst — Tue, 11 Jul 2017 05:29:18 GMT

The Text() must be in the LOAD part of the statement, not in the SQL part. Like this

Data:

LOAD

Text(myField) as LiteralTextValue,

AnotherField,

...

;

SQL SELECT myField, AnotherField,

...

The LOAD part is executed by QV and supports QV functions. The SQL part is executed by the DB server, and supports the function set for that server.

Re: Import SQL Server varchar field as text containing exact value

Wed, 12 Jul 2017 10:51:48 GMT

Thank you Jonathan. This has worked. I didn't have the load section in the script, just the SQL Select script.

I will mark this as the correct answer, but the Actions button isn't working at the moment.