converting number into money format

[#1]

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 $)?

Thank you in advance for your answers.

[#2]

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

[giakoum · Accepted solution]

divide it by 100

[jonathandienst]

Hi

A better way is something like this

LOAD

     ...

     Num(MyField) / 100 As MyField,

     ...

Hope that helps

Jonathan

[#5]

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

[#6]

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

[giakoum]

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

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

Φιλικά

Γιάννης