topic Re: Added zeros to field in QlikView

Added zeros to field

dimak123 — Thu, 19 Nov 2015 08:59:19 GMT

Hi to all

Need help, somehow i am getting field on layout different from original data.

How can i show the exact field?

Re: Added zeros to field

Thu, 19 Nov 2015 10:50:52 GMT

try it once in text object

by using same expression

=len(INVENCOLORID)

Re: Added zeros to field

dimak123 — Thu, 19 Nov 2015 10:54:53 GMT

Re: Added zeros to field

Gysbert_Wassenaar — Thu, 19 Nov 2015 11:10:47 GMT

Try num(Field, '0000')

Re: Added zeros to field

tamilarasu — Thu, 19 Nov 2015 11:12:35 GMT

Hi Dima,

Paste the code above the sql statement.

Load *,

Num(Field) as New;

Re: Added zeros to field

dimak123 — Thu, 19 Nov 2015 11:15:29 GMT

Num' is not a recognized built-in function name.

Re: Added zeros to field

dimak123 — Thu, 19 Nov 2015 11:17:21 GMT

SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]'Num' is not a recognized built-in function name.

SQL SELECT

len(INVENTCOLORID) as Length,

INVENTCOLORID as Field,

Num(INVENTCOLORID) as New

FROM

Re: Added zeros to field

raghvendrasingh — Thu, 19 Nov 2015 11:17:30 GMT

Try this:

Tab1:

SQL SELECT

len(INVENTCOLORID) as length,

INVENTCOLORID as Field

from "DNA_PROD".dbo.NAOTMOBILEITEMS;

noconcatenate

Tab2:

Load length,

text(Field) as Field

resident Tab1;

Drop tab Tab1;

Re: Added zeros to field

jonathandienst — Thu, 19 Nov 2015 11:20:43 GMT

Do you want to preserve the exact number of leading zeroes? Then do this:

LOAD text(Field) As Field,

Length;

SQL SELECT

Len(..... rest of your SQL expression ....

;

Re: Added zeros to field

dimak123 — Thu, 19 Nov 2015 11:21:34 GMT

still the same

Re: Added zeros to field

dimak123 — Thu, 19 Nov 2015 11:23:30 GMT

it worked!!!
thanks!!!

Re: Added zeros to field

oknotsen — Thu, 19 Nov 2015 11:33:05 GMT

Num'

Try it without that single quote, because it actually is a recognized built-in function name.

I use it almost every day.

Re: Added zeros to field

Gysbert_Wassenaar — Thu, 19 Nov 2015 11:37:43 GMT

Yes, as you've noticed telling SQL Server to execute Qlikview functions doesn't work. You can use a preceding load like shown above in Jonathan‌‌'s post.

Re: Added zeros to field

tamilarasu — Thu, 19 Nov 2015 11:44:57 GMT

You should put the code above your sql statement. I.e Preceding load. Anyhow, you got the solution.

Re: Added zeros to field

Gysbert_Wassenaar — Thu, 19 Nov 2015 11:46:37 GMT

In T-SQL? Don't you mean the Format function perhaps?

Re: Added zeros to field

oknotsen — Thu, 19 Nov 2015 11:58:24 GMT

No, in the preceding load.

Paste the code above the sql statement.

Load *,

Num(Field) as New;

Re: Added zeros to field

Gysbert_Wassenaar — Thu, 19 Nov 2015 12:13:48 GMT

Never mind. I thought you were responding to his sql-server error message. See you on monday