Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i need an elegant way to format an amount in smallest unit in script to store these data in an csv file.
I have a qvd with fields amount "237,99" and currency "EUR". I must Export these data to an csv file with amount in smalles unit.
It is difficult to Format amounts for Currencies like YPN because these currency have no decimals.
At the moment i used following script:
Num(Replace( Num(Amount, '0,00'),',','')) as Amount_Num,
But there is no Differentiation between Currency EUR and Currency YPN.
I dont think that if - clause is a solution because i assume Performance-disturbing.
Did anybody know how i can solve these Problem?
Input QVD:
Amount | Currency |
---|---|
237,99 | EUR |
10200,00 | YPN |
Output CSV:
Amount_Num | Currency |
---|---|
23799 | EUR |
10200 | YPN |
Many thanks for your help!
Regards
Onkellepie
try like this:
Num( Num#(Amount), '0,00')') as Amount_Num,
according to your filed try like
num(num#(FIELD))
Multiply the values by 100 before storing them in the csv file.
Hi,
thanks for your fast answer. I think i dont understand you correctly. Because if i dont Format and multiple by 100 i get as Output:
Amount_Num | Currency |
---|---|
23799,00 | EUR |
1020000,00 | YPN |
If i Format and multiple by 100 i get as Output:
Amount_Num | Currency |
---|---|
2379900 | EUR |
1020000 | YPN |
Both is wrong. Could you please explain in Detail your solution?
Many thanks!
Regards
The format you are loading seems a text, so you must format as a number first and then multiply by 100.
Hi,
What is your decimal seperator???
if it is ,
try like
write if(Currency='YPN',num(fieldname,'###'),num(fieldname*100,'###'))
Regards
try like this:
Num( Num#(Amount), '0,00')') as Amount_Num,
according to your filed try like
num(num#(FIELD))
Hi,
thanks. That works for JPY but not for EUR.
i got:
Amount_Num | Currency |
---|---|
237,99 | EUR |
1020 | JPY |
So now i use the if and your number Format:
if(MixMatch(Currency, 'JPY'), Num( Num#(Amount)), Num(Replace( Num(Amount, '0,00'),',',''))) as Amount_Num,
Thanks for help to all! If anybody knows better solution please let me know.
Regards
Fabian