topic Re: SQL SELECT causes problem. in QlikView

SQL SELECT causes problem.

Anonymous — Wed, 26 Aug 2015 08:53:39 GMT

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!

Re: SQL SELECT couses problem.

marcus_sommer — Wed, 26 Aug 2015 09:10:49 GMT

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

Re: SQL SELECT couses problem.

sasiparupudi1 — Wed, 26 Aug 2015 09:17:16 GMT

try Num(ID) as ID in your script

Re: SQL SELECT couses problem.

Ralf-Narfeldt — Wed, 26 Aug 2015 09:38:16 GMT

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...

Re: SQL SELECT couses problem.

Anonymous — Wed, 26 Aug 2015 10:29:11 GMT

And this is the answer.

Thank you all very much

Re: SQL SELECT causes problem.

jldengra — Thu, 27 Aug 2015 11:27:27 GMT

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.

Re: SQL SELECT causes problem.

sasiparupudi1 — Thu, 27 Aug 2015 11:31:33 GMT

please close this thread by selecting the correct answer