Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Specialist
Partner - Specialist

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

18 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

try this:

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

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

Regards,

Jagan

alexis
Partner - Specialist
Partner - Specialist
Author

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

alexis
Partner - Specialist
Partner - Specialist
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi Alexis,

Check this, it is working for me

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

Regards,

Jagan.

alexis
Partner - Specialist
Partner - Specialist
Author

I get 12,34567

I need to get 12.34567

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

Alexis

Not applicable

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

alexis
Partner - Specialist
Partner - Specialist
Author

Thanks Joe

Same result: 12,34567

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

Alexis

Michiel_QV_Fan
Specialist
Specialist

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