topic Re: Extract an number wich begin by an 0 as an string in QlikView

Extract an number wich begin by an 0 as an string

Wed, 12 Nov 2014 08:42:49 GMT

Hello,

i met an problem i have in a customer table an strange id.

It s an number wich begins by 0 so for two customer :

customer A id = 133

customer B id = 0133

customer C id = 0133

But the problem is that qlikview, when i extract the data, considers that the id is an number.

So after the extraction of the data, i have in my qvd data the id for the 3 customer

customer A id=133

customer B id = 133

customer C id = 133

how to fix that problem and to get qlikview consider the id as an string ?

Thanks

Re: Extract an number wich begin by an 0 as an string

Anonymous — Wed, 12 Nov 2014 08:45:21 GMT

Perform a Trim on the data field. Trim is a sting function so that should make the script engine interpret this as a sting.

Trim(CUSTOMERFIELD) as Customer Field

Re: Extract an number wich begin by an 0 as an string

rubenmarin — Wed, 12 Nov 2014 08:45:59 GMT

Hi Pierre, you need to use Text funtion in script, ie:

LOAD Text(id) as id

FROM...

Re: Extract an number wich begin by an 0 as an string

alexandros17 — Wed, 12 Nov 2014 08:46:21 GMT

try num(customer_id)

Re: Extract an number wich begin by an 0 as an string

Anonymous — Wed, 12 Nov 2014 08:51:23 GMT

You might want to consider why you want these fields as a sting in the first case. Perhaps you should force them all to numbers (by multiplying them).

Putting these ID's as numbers will allow you to do more with them in certain cases (like selecting a range between ID numbers etc)

Re: Extract an number wich begin by an 0 as an string

jonathandienst — Wed, 12 Nov 2014 08:54:44 GMT

Hamish Imrie wrote:

You might want to consider why you want these fields as a sting in the first case. Perhaps you should force them all to numbers (by multiplying them).

Unless of course there is genuinely a difference between code 0133 and 133. Then using text() is the correct approach as posted above by Ruben.

Jonathan

Re: Extract an number wich begin by an 0 as an string

Anonymous — Wed, 12 Nov 2014 08:58:14 GMT

True Jonathan, that might be the case (I have seen some of these bad examples....).

Re: Re: Extract an number wich begin by an 0 as an string

Thu, 20 Nov 2014 13:55:09 GMT

thank for all the replys,

I try to put Text(id) as id .

it seems working cause i ve an id as an string as you can see :

But it's very strange cause i ve met some problem for some id wich begins by an 0 and not for the others.

For example, i ve two No. differents in the database

No.= '0138' for record 1

No.='138' for record 2

and in my QVD or qvw i find only the No.=138 for the two record

Do you have any idea how i could solve this strange issue ?

thanks

Re: Extract an number wich begin by an 0 as an string

rubenmarin — Thu, 20 Nov 2014 14:03:57 GMT

Hi, QV always try to convernt data to number, so in a normal load, 0138 and 138 are both stored as the number 138, when creating the qvd you need to specify Text function to keep it in text format.

You also need to apply the Text() Function when you are creating the qvd.

Hope this helps.