topic Re: 0 in front of data when loading from SQL in QlikView

0 in front of data when loading from SQL

Fri, 06 Jun 2014 09:38:47 GMT

Hi,

I have a strange result when loading date from a SQL database into qlikview.

In my database i have a field containing numbers and letters. It's a nvarchar column. When i import the data in qlikview, qlikview is adding somethimes '0' to that column in front of my data.

Does anybody know how to resolve that problem?

Thank you

Michael

Re: 0 in front of data when loading from SQL

Anonymous — Fri, 06 Jun 2014 09:43:22 GMT

Michael

Would formatting it with the num() function help ?

= num ( '01' , '#0')

Bill

Re: 0 in front of data when loading from SQL

Fri, 06 Jun 2014 09:47:08 GMT

Bill,

No because we have data that are 'L52' or 'P9999'

Michael

Re: 0 in front of data when loading from SQL

MayilVahanan — Fri, 06 Jun 2014 09:47:19 GMT

Try like this

Load text(CODEFAM) as CODEFAM, LIBFAMFR, LIBFAMNL;

Sql Select * from Txt;

Re: 0 in front of data when loading from SQL

its_anandrjs — Fri, 06 Jun 2014 09:50:10 GMT

Your field CODEFAM is string field thats why you get 0 in front some values also this field has character and the numbers also so you are nor able to convert this into the number also. Try to make it text string like Text(CODEFAM).

Load

Text(CODEFAM) as CODEFAM

From Location;

Re: 0 in front of data when loading from SQL

Anonymous — Fri, 06 Jun 2014 09:56:37 GMT

Michael

How about using the isnum() function ?

You can test by putting these expression in a text box :

= if ( isnum( '01' ) , num ( '01' , '#0') , text ('01' ) )

= if ( isnum( 'L52' ) , num ( 'L52' , '#0') , text ('L52' ) )

Re: 0 in front of data when loading from SQL

Fri, 06 Jun 2014 10:01:55 GMT

I have tested by putting text but i have still the same problem

Code :

Load

Text(CODEFAM) as CODE,

LIBFAMFR as LIBFAM

RESIDENT TMP;

Re: 0 in front of data when loading from SQL

Fri, 06 Jun 2014 10:07:35 GMT

It is working but it's not de result i want.

I have to keep some data like 01,02,03.....

Re: 0 in front of data when loading from SQL

Anonymous — Fri, 06 Jun 2014 10:12:55 GMT

Michael

Can you identify which 01 's you want as 01 and which as just 1 ?

Re: 0 in front of data when loading from SQL

Tue, 10 Jun 2014 10:57:01 GMT

Hi Bill,

No i can't.