Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

onkellepie
New Contributor III

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

Many thanks for your help!

Regards

Onkellepie

1 Solution

Accepted Solutions

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

6 Replies

Re: Need to format amount in smallest unit

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


talk is cheap, supply exceeds demand
onkellepie
New Contributor III

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

Both is wrong. Could you please explain in Detail your solution?

Many thanks!

Regards

fvelascog72
Valued Contributor

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,

What is your decimal seperator???

if it is ,

try like

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

Regards

Regards,
Prashant Sangle

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

onkellepie
New Contributor III

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

Community Browser