topic Re: Commas and dots formatting in QlikView

Commas and dots formatting

alexis — Mon, 16 Feb 2015 01:20:52 GMT

In my locality we use a dot as a thousand separator an a comma for decimal.

i.e.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€#.##0,00;-€#.##0,00';

The number thirty thousand one hundred point 40 would be represented as:

34.100,40

This is correct so far. However, I have a map extension which expects numbers in the more "traditional" format of commas for thousands and point for the decimal part so 34,100.40 for the above number

I have a field (lets call it Lat) which arrives as a 7 digit number (e.g. 1234567) which need to be divided by 100000 and be saved as "12.34567")

I have tried:

= num#((1234567/100000), '#.#', '.', ',') and it does not appear to work - it always returns "12,34567"

any ideas

Alexis

Re: Commas and dots formatting

Oleg_Troyansky — Mon, 16 Feb 2015 01:50:14 GMT

try this:

num(1234567/100000, '#.##0')

Re: Commas and dots formatting

jagan — Mon, 16 Feb 2015 02:59:08 GMT

Hi,

Try this

= Num(1234567/100000, '###0.00000', '.' )

Regards,

Jagan

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 07:57:21 GMT

Hi Oleg

Your suggestion returns a result of "12"

I am expecting the result to be "12.34567"

Please remember that my "default" setup uses "." for thousands and commas for decimals.

Thanks for responding.

Alexis

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 09:30:06 GMT

Hi Jagan

Your suggestion returns a result of "0.00012"

I am expecting the result to be "12.34567"

Please remember that my "default" setup uses "." for thousands and commas for decimals.

Thanks for responding.

Alexis

Re: Commas and dots formatting

jagan — Mon, 16 Feb 2015 11:04:07 GMT

Hi Alexis,

Check this, it is working for me

=Num#(1234567/100000, '###0,00000', ',')

Regards,

Jagan.

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 11:23:22 GMT

I get 12,34567

I need to get 12.34567

Thanks for trying - I'll have to find an alternative solution..

Alexis

Re: Commas and dots formatting

Mon, 16 Feb 2015 11:31:47 GMT

Hi Alexis,

you just need to change the function used

=Num#(1234567/100000, '###0.00000', '.' )

should give you the desired result

hope that helps

Joe

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 12:10:32 GMT

Thanks Joe

Same result: 12,34567

I have different regional settings to you I am fairly certain...

Alexis

Re: Commas and dots formatting

Michiel_QV_Fan — Mon, 16 Feb 2015 12:31:41 GMT

You could use replace in more than 1 step. Like this:

1 replace the . with something

2 replace the , with a .

3 replae something with a ,

3 2 1

replace(replace(replace(<your field>, '.', '_'), ',','.'), '_',',')

Re: Commas and dots formatting

jonathandienst — Mon, 16 Feb 2015 12:35:24 GMT

Alexis

I tried the inverse - my regional settings are . for decimal and <space> for 000s, so I tried to make it display the other way round:

Environment:

SET ThousandSep=' ';

SET DecimalSep='.';

The expression

=num((1234567/100000), '#.#'')

returns 12.3

=num((1234567/100000), '#,#', ',', '.')

return 12,3

So one would think it should work for you in the opposite direction. Make sure you run the load to apply the environment settings. What version are you running?

Jonathan

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 13:12:22 GMT

Hi Jonathan

Yes your solution produced the desired result but I cannot change the regional settings universally in my application.

My requirement is driven by the fact that the mapping extension that I am using is expecting coordinates in the form 99.9999999

Thanks for helping out

Alexis

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 13:13:29 GMT

Hi Michiel,

The replace method is good but it creates a string field. I need to pass these values to an API (that expects 99.999999) as a numeric.

Thanks

Alexis

Re: Commas and dots formatting

Mon, 16 Feb 2015 13:34:38 GMT

Strange, I changed my settings when testing.

Can you upload an example? Quite interesting that didn't work

Re: Commas and dots formatting

alexis — Mon, 16 Feb 2015 13:39:08 GMT

Hi again Joe

What are your environment settings?

Alexis

Re: Commas and dots formatting

Mon, 16 Feb 2015 14:14:46 GMT

Hi Alexis,

I just used the same set as yourself

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';

Re: Commas and dots formatting

jagan — Tue, 17 Feb 2015 02:10:54 GMT

Hi Alexis,

If you attach the sample file with your regional settings then it would be easier to give the solution for us.

Regards,

Jagan.

Re: Commas and dots formatting

alexis — Tue, 17 Feb 2015 09:24:18 GMT

Hi Jagan

I created a new application and put everyone's suggestions on it - and voila!!

For some reason it was not working for me within the application that I am creating so my problem is elsewhere and not with the formatting..

Thank you Jagan and Jow for your suggestions/help

Alexis

Re: Commas and dots formatting

alexis — Tue, 17 Feb 2015 09:25:49 GMT

Hi Joe

I created a new application (at Jagan's request) and put everyone's suggestions on it - and voila!!

For some reason it was not working for me within the application that I am creating so my problem is elsewhere and not with the formatting..

Thank you and Jagan for your suggestions/help

Alexis