topic Re: Problem with number format in QlikView

Problem with number format

Anonymous — Mon, 20 Apr 2015 14:08:57 GMT

Hello:

I'm having the following problem with number formats:

This is the number with its original format: 123456789

what I need to do is get rid of the last number so it can be like this: 12345678

to do so, i use the following script: If(Len(number)=9,Left(number,8).

The problem is when I have this number: 0123456789

I have to get rid of the last and the first number so I can get: 12345678

Help please

Thank you very much!

Re: Problem with number format

marcus_malinow — Mon, 20 Apr 2015 14:10:41 GMT

try this:

if(len(num(number)) = 9, left(num(number, 8)))

Re: Problem with number format

datanibbler — Mon, 20 Apr 2015 14:14:08 GMT

Hi,

no problem at all. Does the first part work?

You can use another IF_construct to query whether the first digit is a 0 (correct?) and if that's the case, remove the first digit using the MID() function: >> MID([field], 2, (LEN([field]-1)) <<

HTH

Re: Problem with number format

MK_QSL — Mon, 20 Apr 2015 14:14:24 GMT

Left(Num(NumberField),8) as NumberField

Re: Problem with number format

sunny_talwar — Mon, 20 Apr 2015 14:16:45 GMT

Try this:

Table1:

LOAD Left(Num(Number), 😎 as Number;

LOAD * INLINE [

Number

0123456789

987654321

];

Best,

Sunny

Re: Problem with number format

datanibbler — Mon, 20 Apr 2015 14:26:33 GMT

Hi sunindia,

that's good. I didn't know the num() function does that, although it might be logical ...

Just for the record (I'm a fan of rather extensive documentation so anyone can read my code and understand what it does without running it) - the num() function drops leading zeros, so you don't have to mess with the MID() function and such.

Best regards,

DataNibbler

Re: Problem with number format

Anonymous — Mon, 20 Apr 2015 14:29:02 GMT

hi, try it

if (len(num(fielname))=9,left(fielname,8),mid(fieldname,2,8))