topic Re: num function in QlikView

num function

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

Hi,

I have 2 separate fields that I'n loading from a fixed length file. Both are numbers, and in the fixed length file we use leading zeroes.

The one field is 9 characters in length and the other is 15. I'm trying to load them in QV and turn them to numbers, dropping the zeroes, but this only wants to work for the 9-digit field. For the 15 digit field it returns NULL.

num(@42:50) as [No of Instalments], // this works fine and returns e.g. 12 from 000000012

num(@60:74) as [Total of Instalments, INT & Fees], // this doesn't work and returns NULL from 000000000123456

Re: num function

its_anandrjs — Thu, 31 Aug 2017 14:08:03 GMT

Try with

Num(@60:74,'################') as [Total of Instalments, INT & Fees],

Re: num function

Peter_Cammaert — Thu, 31 Aug 2017 14:09:37 GMT

Two remarks:

num() is not a conversion function. It's a formatting function that tries to shape values that have already been recognised by QlikView before. Use num#() to translate strings into numbers.
QlikView functions cannot handle string representations with more than 14 digits. You'll have to use a few cutting functions to omit the leading zeroes before you can force QlikView to convert the remainder into a dual value.

Best,

Peter

Re: num function

its_anandrjs — Thu, 31 Aug 2017 14:10:05 GMT

Try for both this way

num(@42:50,'#########') as [No of Instalments],

num(@60:74,'###############') as [Total of Instalments, INT & Fees]

Re: num function

antoniotiman — Thu, 31 Aug 2017 14:11:00 GMT

Hi Gerhards,

try

Num(Right(@60:74,14))

Num(@61:74)

Re: num function

gerhardl — Thu, 31 Aug 2017 14:11:03 GMT

still returns null.

up to 14 characters works 100%, but 15+ the num function just doesn't wanna work

Re: num function

sunny_talwar — Thu, 31 Aug 2017 14:11:06 GMT

May be load it as text, because QlikView can only read numbers up until 14 digits

Re: num function

gerhardl — Thu, 31 Aug 2017 14:13:40 GMT

Thanks - this explains it really well. I marked Antonio's answer as correct because it provides a final proposal that works, but your explanation really helps

Re: num function

Peter_Cammaert — Thu, 31 Aug 2017 14:15:07 GMT

Helpfuls can be assigned using the corresponding entry in the Actions menu (bottom left)

Re: num function

gerhardl — Thu, 31 Aug 2017 14:22:16 GMT

I'm trying to do so, but the available actions won't load - just get this for minutes on end. Might be a browser issue... sorry, will try again later:

Re: num function

Peter_Cammaert — Thu, 31 Aug 2017 14:30:02 GMT

No problem. Such things happen