Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
klewandowski
New Contributor III

SQL SELECT causes problem.

Hello.

My new problem with Qlikview is as following:

I Have table in SQL with column ID that values are:

0 01 02 03 04 05 06 07 08 09 1 10 11 12 13 3 99 (nvchar)

I implement tables with sql select statement.

But!

When I load it in QV I see this table but its column has values:

0 01 02 03 04 05 06 07 08 09 10 11 12 13 99

So that QV adds '0' to single numbers even numer format is nvchar... It completely ruins my hierarchy tree. How to solve it without creating new table or using update statement (I have no access)

counting on you!

6 Replies

Re: SQL SELECT couses problem.

QlikView didn't care about such special data-types - there are only numeric or string respectively the combination of both as dual-field. But this isn't a problem rather the opposite. You could solve it with converting/formating with functions like num(num#(Field, 'Format'), 'Format') your data within a : Preceding Load.

- Marcus

sasiparupudi1
Honored Contributor III

Re: SQL SELECT couses problem.

try Num(ID) as ID in your script

Employee
Employee

Re: SQL SELECT couses problem.

If you want ID to be interpreted as a text field, transform it in a preceding load to the SQL:

LOAD Text(ID) As ID, Field, Field2...;

SQL SELECT

ID, Field, Field2...

klewandowski
New Contributor III

Re: SQL SELECT couses problem.

And this is the answer.

Thank you all very much

jldengra
Contributor

Re: SQL SELECT causes problem.

I had a similar problem with postal codes. Sometimes and apparently arbitrarily, QlikView loaded the values adding 0's to the left. For example, the value 7500 was sometimes loaded as 07500 and other times as 7500 in records from the same source having the value 7500.

After delving, the issue was related to nonprintable characters, and I solved it by removing all of them in the query, namely replacing CHAR(9), CHAR(13), CHAR(10) and CHAR(163) with the empty string '', as well as applying RTRIM and LTRIM functions to trim the result.

sasiparupudi1
Honored Contributor III

Re: SQL SELECT causes problem.

please close this thread by selecting the correct answer

Community Browser