6 Replies Latest reply: Apr 7, 2015 6:55 AM by Fabian Lepie

# Need to format amount in smallest unit

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:

AmountCurrency
237,99EUR
10200,00YPN

Output CSV:

Amount_NumCurrency
23799EUR
10200

YPN

Regards

Onkellepie

• ###### Re: Need to format amount in smallest unit

Multiply the values by 100 before storing them in the csv file.

• ###### Re: Need to format amount in smallest unit

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_NumCurrency
23799,00EUR
1020000,00

YPN

If i Format and multiple by 100 i get as Output:

Amount_NumCurrency
2379900EUR
1020000

YPN

Many thanks!

Regards

• ###### Re: Need to format amount in smallest unit

The format you are loading seems a text, so you must format as a number first and then multiply by 100.

• ###### Re: Need to format amount in smallest unit

Hi,

if it is ,

try like

write if(Currency='YPN',num(fieldname,'###'),num(fieldname*100,'###'))

Regards

• ###### Re: Need to format amount in smallest unit

try like this:

Num( Num#(Amount), '0,00')') as Amount_Num,

according to your filed try like

num(num#(FIELD))

• ###### Re: Need to format amount in smallest unit

Hi,

thanks. That works for JPY but not for EUR.

i got:

Amount_NumCurrency
237,99EUR
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