6 Replies Latest reply: Jul 27, 2012 5:29 AM by Dimitris Charalampou

converting number into money format

HI there,

i am loading some data to my qlikview project and from a .dat file. Because of this the format of the fields are prespecified and cannot be changed.

I have a field that contains money data and has values like: 00000001005 ( for 10,05 \$), 00000002030 ( for 20,30 \$) etc etc.

How can i convert them into the correct format ( eg 10,05 \$)?

• Re: converting number into money format

I'm afraid that it does not work.

I found yesterday this script that almost does what i want.

the script is the following:

replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField

it transforms 0000001560 into 1560. The correct ammount is 15,6

If the number is 00000078 it displays 78 . the correct should be 0.78.

The correct format should ALWAYS be 0000000xxxxxx --> xxxx,xx.

The last two digits are ALWAYS  the decimals.

So how can i make so that the last two digits will always be after the decimal point?

Thank you

• Re: converting number into money format

divide it by 100

• Re: converting number into money format

Actually it was so easy. Thanks Gianni. Πόσο απλό και όμως το μυαλό σου πάει σε περίπλοκες λύσεις για να διαιρέσεις με το 100...

• Re: converting number into money format

Εχω φαει απειρες ωρες προσπαθωντας να λύσω κάτι που τελικά είναι προφανές, δεν είσαι ο μόνος... .

Άτιμο επάγγελμα...

Φιλικά

Γιάννης

• Re: converting number into money format

Hi

A better way is something like this

...

Num(MyField) / 100 As MyField,

...

Hope that helps

Jonathan

• Re: converting number into money format

Try this Num(MyField/100,'#,##0.00 \$')