topic Re: Format Text to Number in QlikView

Format Text to Number

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following values in a field (they are formatted as text, aligned at left in data):

00000000000000026287

00000000000000018306

00000000000000019008

00000000000002726211

00000000000003521228

What I need is to set them like this (taking out the zeros and format them to number):

26287

18306

19008

2726211

3521228

Is there any way to do that?

Thank you!!!

---

Script:

TABLE:

LOAD

TEXT(F1) AS F1_NUM;

LOAD * INLINE [

00000000000000026287

00000000000000018306

00000000000000019008

00000000000002726211

00000000000003521228

];

EXIT SCRIPT

Re: Format Text to Number

Anil_Babu_Samineni — Tue, 17 Apr 2018 13:36:11 GMT

Can you use Field * 1 ???

Re: Format Text to Number

sunny_talwar — Tue, 17 Apr 2018 13:36:39 GMT

May be try this

Num#(Replace(LTrim(Replace(FieldName, '0', ' ')), ' ', '0')) as FieldName

Re: Format Text to Number

MK_QSL — Tue, 17 Apr 2018 13:38:31 GMT

Num(Evaluate(FieldName)) as FieldName

Re: Format Text to Number

sunny_talwar — Tue, 17 Apr 2018 13:41:12 GMT

Both works... but yours is much cleaner

TABLE:

LOAD F1,

Num#(Replace(LTrim(Replace(F1, '0', ' ')), ' ', '0')) as F1_Sunny,

Num(Evaluate(F1)) as F1_Manish;

LOAD * INLINE [

00000000000000026287

00000000000000018306

00000000000000019008

00000000000002726211

00000000000003521228

];