Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Import SQL Server varchar field as text containing exact value

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.

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
devarasu07
Master II
Master II

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

Not applicable
Author

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.

Siva_Sankar
Master II
Master II

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

jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Not applicable
Author

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.