topic Re: Number formatting on edit script in QlikView

Number formatting on edit script

Thu, 30 Jun 2016 08:29:14 GMT

Hello,

I am trying to load a .csv file to QliKView and I have managed to do that.

But I noticed that my numberformat was not right.

The output is at the moment : 1.000,00 etc

It needs to be 1000,00

There will be other datasources too, so I have not changed the thousand separator SET on the beginning from space to dot.

Is there a way to put there multiple thousandSep options?

Or does someone have a proper solution for this with the num() function? I tried, and failed.. it just brings the numbers before the thousandspearator to the view..

Cheers,

Niko

Re: Number formatting on edit script

Frank_Hartmann — Thu, 30 Jun 2016 08:34:09 GMT

try something like:

=num(YourFiled,'####,##') as YourField

Re: Number formatting on edit script

marcus_sommer — Thu, 30 Jun 2016 08:37:43 GMT

If there is the need to change the decimal-delimiter and/or the thousand-separator you will need the specify both parameters - only one parameter will be simply ignored. By using you output-format the following would work:

= num(num#('1.000,00', '#.###,##'), '####,##', ',', '')

- Marcus

Re: Number formatting on edit script

Thu, 30 Jun 2016 09:19:17 GMT

Hi,

Thank you for your input. I tried your suggestion, but still it leaves the numbers over 1000 out from the field. So the numbers that have the format 1.000,00 or 1.000.000,000 .. These are on the left side if I take a look on the original field on the list box. So they are strings?

Cheers,

Niko

Re: Number formatting on edit script

Thu, 30 Jun 2016 09:23:09 GMT

Actually, the example does not even work for me for some reason ..

Re: Number formatting on edit script

Thu, 30 Jun 2016 09:27:47 GMT

Well, I simply replaced the . with empty space on the loadscript

replace(field, '.', ' ')

Re: Number formatting on edit script

marcus_sommer — Thu, 30 Jun 2016 09:28:43 GMT

Are they displayed on the left side they will be interpreted as strings and you will need a solution like my example above with a num(num#()) expression to convert at first the value and then format it. For larger numbers you need to increase the number of # and/or you will need a switch within the num#() to detect the right conversion. I mean something like this:

= num(num#('1.000,00', '#.###.###,##'), '####,##', ',', '')

= num(alt(num#('1.000,00', '#.###.###,##'), num#('1.000,00', '#.###,##')), '####,##', ',', '')

- Marcus