topic Re: Need to format amount in smallest unit in QlikView

Need to format amount in smallest unit

onkellepie — Tue, 07 Apr 2015 09:36:59 GMT

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

Re: Need to format amount in smallest unit

Gysbert_Wassenaar — Tue, 07 Apr 2015 09:44:05 GMT

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

Re: Need to format amount in smallest unit

onkellepie — Tue, 07 Apr 2015 09:58:28 GMT

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

Re: Need to format amount in smallest unit

fvelascog72 — Tue, 07 Apr 2015 10:01:44 GMT

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

PrashantSangle — Tue, 07 Apr 2015 10:12:05 GMT

Hi,

What is your decimal seperator???

if it is ,

try like

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

Regards

Re: Need to format amount in smallest unit

avinashelite — Tue, 07 Apr 2015 10:14:57 GMT

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

onkellepie — Tue, 07 Apr 2015 10:55:02 GMT

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