Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Thank you in advance for your answers.

1 Solution

Accepted Solutions
giakoum
Partner - Master II
Partner - Master II

6 Replies
Not applicable
Author

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
Partner - Master II
Partner - Master II

divide it by 100

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

A better way is something like this

LOAD

     ...

     Num(MyField) / 100 As MyField,

     ...

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

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

giakoum
Partner - Master II
Partner - Master II

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

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

Φιλικά

Γιάννης