topic Re: Currency/Money formatting in QlikView

Currency/Money formatting

Wed, 06 Oct 2010 17:39:55 GMT

Hi All,

I'm importing a text file into QV and 1 field just won't format correctly. The data is money in European format of ##.##,00-. The format should be displayed as US standard (##,##.00). Here are a few examples of the data:

4.950,00- should be (4,950.00)

40.000,00 should be 40,000.00

I've tried to change the MoneyFormat, money#,num#,money,num but it just won't work...

Any suggestions?

Thank you!

Sarah[:)]

Currency/Money formatting

Wed, 06 Oct 2010 18:15:24 GMT

How about this:

Money:
LOAD * INLINE [
 Dimmer, Val
 A, "4.950,00-"
 B, "40.000,00"
];
NewMoney:
LOAD Dimmer As NewDim, Num#(If(Right(Val, 1) = '-', '-') &
 Replace(Replace(Replace(Replace(Val, ',', '%'), '.', ','), '%', '.'), '-', ''))
 As NewVal
RESIDENT Money;

It's pretty messy, but it seems to work on both positive and negative numbers. I'm not sure if there is an easier way using one of the built in functions.

Re: Currency/Money formatting

Thu, 05 Sep 2013 23:31:14 GMT

Hello all,

I have attached some images showing my settings in QlikView and sample data from our SAP Enterprise client. My question is where/how to manage the settings for decimal places in a particular column which is not available in the Chart Properties that I could locate. QlikView shows 4 decimal places and our SAP system allows only two (except in yen where there are none).

Thank you in advance.

Kind regards,

Lucianne

Re: Currency/Money formatting

rwunderlich — Thu, 05 Sep 2013 23:45:54 GMT

If List Price is a Dimension, you can change the number formatting in Settings, Document Properties, Number pane.

You can also fix the format in the script.

-Rob

Re: Currency/Money formatting

Tue, 10 Sep 2013 17:55:06 GMT

Rob, I used your first suggestion and it worked. Just wanted to thank you. Cheers!