Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
onkellepie
Partner - Contributor III
Partner - 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
avinashelite

try like this:

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


according to your filed try like

num(num#(FIELD))

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
onkellepie
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Specialist
Partner - Specialist

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

PrashantSangle

Hi,

What is your decimal seperator???

if it is ,

try like

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
avinashelite

try like this:

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


according to your filed try like

num(num#(FIELD))

onkellepie
Partner - Contributor III
Partner - Contributor III
Author

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