topic Re: converting number into money format in QlikView

converting number into money format

Thu, 26 Jul 2012 16:09:15 GMT

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.

Fri, 27 Jul 2012 08:48:20 GMT

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 — Fri, 27 Jul 2012 08:49:43 GMT

divide it by 100

jonathandienst — Fri, 27 Jul 2012 09:24:45 GMT

A better way is something like this

LOAD

...

Num(MyField) / 100 As MyField,

...

Hope that helps

Jonathan

Fri, 27 Jul 2012 09:29:30 GMT

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

Fri, 27 Jul 2012 09:29:34 GMT

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

giakoum — Fri, 27 Jul 2012 09:37:28 GMT

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

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

Φιλικά

Γιάννης