Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
divide it by 100
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
divide it by 100
Hi
A better way is something like this
LOAD
...
Num(MyField) / 100 As MyField,
...
Hope that helps
Jonathan
Try this Num(MyField/100,'#,##0.00 $')
Actually it was so easy. Thanks Gianni. Πόσο απλό και όμως το μυαλό σου πάει σε περίπλοκες λύσεις για να διαιρέσεις με το 100...
Εχω φαει απειρες ωρες προσπαθωντας να λύσω κάτι που τελικά είναι προφανές, δεν είσαι ο μόνος... .
Άτιμο επάγγελμα...
Φιλικά
Γιάννης