topic Re: Zero Problems in QlikView

Zero Problems

johnan — Mon, 10 Feb 2020 13:03:16 GMT

Hi,

I have in my database a field that contains a 16 characters.

In table 1 real data it is 0000000032043988

In table 2 it is just 32043988 and stripped away all zeros in database.

In qlikview you can use Right(Field,8) to convert.

Can i use som function to strip all the zeros from table 1 instead?

John

Re: Zero Problems

tresB — Mon, 10 Feb 2020 13:07:52 GMT

You can use num() to get rid of leading zeros. I.e. -

Num(0000000032043988) - gives 32043988

Re: Zero Problems

johnan — Mon, 10 Feb 2020 13:33:44 GMT

Hi, try that, it gives me nothing 😞

Num(TRTREF) as [Pick control number], give QlikView "-"
TRTREF as [Pick control number2], give QlikView "0000000032043988"

I think the field is text in DB

Re: Zero Problems

tresB — Mon, 10 Feb 2020 13:37:22 GMT

Are the quotes part of your data (you see in qv)? If so, you could try like:

Num(Keepchar(TRTREF, '0123456789')) as [Pick control number]

Re: Zero Problems

johnan — Mon, 10 Feb 2020 13:42:45 GMT

Hi,

No, no quotes .

The data is : 0000000035257451 and it text i guess

Still doesn't work

Re: Zero Problems

marcus_sommer — Mon, 10 Feb 2020 14:21:11 GMT

Only values with max. 15 digits are numbers in Qlik - if the values are processed in any way it are only 14 digits. This means your values couldn't be directly formatted in your wished way and also not be converted - the conversion worked only if it happens within their limits.

But you could use string-functions to remove the leading zero's, maybe something like this:

num(mid('0000000035257451', findoneof('0000000035257451', '123456789')))

- Marcus

Re: Zero Problems

johnan — Mon, 10 Feb 2020 14:50:52 GMT

This works!

Thanx Man!

John