topic Re: How to convert Number to Text in QlikView

How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:12:39 GMT

I Have to convert 16 digit number into text.Because Csv&Excel supports only 15digit numeric number.

ex:-

4567321789012347(qlikview) this number coming like 4567321789012340(csv&excel) . last digit coming ZERO

Re: How to convert Number to Text

tresB — Wed, 23 Apr 2014 13:14:44 GMT

Text()?

Re: How to convert Number to Text

ariel_klien — Wed, 23 Apr 2014 13:15:09 GMT

Hi,

Did you try Text(Number_Field)

Ariel

Re: How to convert Number to Text

Wed, 23 Apr 2014 13:15:45 GMT

If its the Qlik result you want use TEXT(NUMBERFIELD) AS TextNumField

Re: How to convert Number to Text

ashfaq_haseeb — Wed, 23 Apr 2014 13:18:04 GMT

Try using num# function

num#(4567321789012347,'###########,###')

Or text function()

text(4567321789012347)

Both of them worked for me

Hope it helped.

Regards

ASHFAQ

Re: How to convert Number to Text

MK_QSL — Wed, 23 Apr 2014 13:19:45 GMT

Text(YourNumberField) as YourNumberField

Re: How to convert Number to Text

its_anandrjs — Wed, 23 Apr 2014 13:21:04 GMT

Use Text() function

Text(YourNumberField) as FieldName

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:32:26 GMT

sample code:

LOAD * INLINE [

sno, name

8520000197286155, chiru

8520000197286156, pathi

];

NoConcatenate

b1:

LOAD text(sno) as sno,

name

Resident b;

DROP Table b;

STORE b1 into b.csv(txt,delimiter is ',');

In b.csv file iam getting

sno

8520000197286150,

8520000197286150

last digit coming ZERO

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:33:07 GMT

Yes..

sample code:

LOAD * INLINE [

sno, name

8520000197286155, chiru

8520000197286156, pathi

];

NoConcatenate

b1:

LOAD text(sno) as sno,

name

Resident b;

DROP Table b;

STORE b1 into b.csv(txt,delimiter is ',');

In b.csv file iam getting

sno

8520000197286150,

8520000197286150

last digit coming ZERO

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:33:42 GMT

sample code:

LOAD * INLINE [

sno, name

8520000197286155, chiru

8520000197286156, pathi

];

NoConcatenate

b1:

LOAD text(sno) as sno,

name

Resident b;

DROP Table b;

STORE b1 into b.csv(txt,delimiter is ',');

In b.csv file iam getting

sno

8520000197286150,

8520000197286150

last digit coming ZERO

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:41:48 GMT

sample code:

LOAD * INLINE [

sno, name

8520000197286155, chiru

8520000197286156, pathi

];

NoConcatenate

b1:

LOAD text(sno) as sno,

name

Resident b;

DROP Table b;

STORE b1 into b.csv(txt,delimiter is ',');

In b.csv file iam getting

sno

8520000197286150,

8520000197286150

last digit coming ZERO

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 13:41:58 GMT

sample code:

LOAD * INLINE [

sno, name

8520000197286155, chiru

8520000197286156, pathi

];

NoConcatenate

b1:

LOAD text(sno) as sno,

name

Resident b;

DROP Table b;

STORE b1 into b.csv(txt,delimiter is ',');

In b.csv file iam getting

sno

8520000197286150,

8520000197286150

last digit coming ZERO

Re: How to convert Number to Text

Roop — Wed, 23 Apr 2014 13:53:28 GMT

This is a problem within Excel and not QlikView. The only way in which I can see to do this is to put some form of character on the from of the exported number.

The problem is that Excel will always convert a number type of field into a number and then it does not have enough significant places to hold the full number.

So the best that I can do is prepend an alphabetic character to the front of the number.

Note: even using the single quote at the front of the number does not work the way that Excel would normally operate

Possible a different CodePage may work but I have not tried that approach

Hope that this helps

Re: How to convert Number to Text

balasundaram — Wed, 23 Apr 2014 14:19:20 GMT

thyank..u

Re: How to convert Number to Text

maxgro — Thu, 24 Apr 2014 00:16:37 GMT

in csv iam getting

Re: How to convert Number to Text

Thu, 24 Apr 2014 02:21:22 GMT

Hi, I tried your sample, seems excel 2003 will display 8520000197286150, but since it's csv file, if you open the file by notepad, the data was stored as

sno,name

8520000197286155,chiru

8520000197286156,pathi

so, i believe it just an excel display issue.

Re: How to convert Number to Text

Thu, 24 Apr 2014 12:35:47 GMT

Hi,

Usually I put another delimiter, like tabulation, so that I can choose format when I convert csv in xlsx. Otherwise, you can try Text(YourNumber) or ''&YourNumber and then store into csv (txt, delimiter is ',', msq).

The msq will put quotes around your number-text and force Excel to interpret it as text.

Hope this helps.

Re: How to convert Number to Text

Thu, 24 Apr 2014 12:45:38 GMT

TEXT()