Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Extract an number wich begin by an 0 as an string

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

8 Replies
hamishimrie
Contributor

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

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

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

try num(customer_id)

hamishimrie
Contributor

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

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)

MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
hamishimrie
Contributor

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

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

Not applicable

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

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 :

id.png



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

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.

Community Browser