Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Commas and dots formatting

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

Tags (4)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Commas and dots formatting

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.

18 Replies
MVP & Luminary
MVP & Luminary

Re: Commas and dots formatting

try this:

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

MVP & Luminary
MVP & Luminary

Re: Commas and dots formatting

Hi,

Try this

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

Regards,

Jagan

Partner
Partner

Re: Commas and dots formatting

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

Partner
Partner

Re: Commas and dots formatting

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

MVP & Luminary
MVP & Luminary

Re: Commas and dots formatting

Hi Alexis,

Check this, it is working for me

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

Regards,

Jagan.

Partner
Partner

Re: Commas and dots formatting

I get 12,34567

I need to get 12.34567

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

Alexis

Not applicable

Re: Commas and dots formatting

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

Partner
Partner

Re: Commas and dots formatting

Thanks Joe

Same result: 12,34567

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

Alexis

michielvandegoo
Valued Contributor

Re: Commas and dots formatting

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>, '.', '_'), ',','.'), '_',',')