Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

0 in front of data when loading from SQL

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.

codefam.png

Does anybody know how to resolve that problem?

Thank you

Michael

9 Replies
Highlighted

Re: 0 in front of data when loading from SQL

Michael

Would formatting it with the num() function help ?

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

Bill

Highlighted
Not applicable

Re: 0 in front of data when loading from SQL

Bill,

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

Michael

Highlighted

Re: 0 in front of data when loading from SQL

HI

Try like this

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

Sql Select * from Txt;

Highlighted

Re: 0 in front of data when loading from SQL

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;

Highlighted

Re: 0 in front of data when loading from SQL

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' ) )

Highlighted
Not applicable

Re: 0 in front of data when loading from SQL

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

Code :

Load

Text(CODEFAM) as CODE,

LIBFAMFR as LIBFAM

RESIDENT TMP;

Highlighted
Not applicable

Re: 0 in front of data when loading from SQL

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

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

Highlighted

Re: 0 in front of data when loading from SQL

Michael

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

Highlighted
Not applicable

Re: 0 in front of data when loading from SQL

Hi Bill,

No i can't.