topic Re: Decimals are treated as Money in QlikView

Decimals are treated as Money

erikkorme — Thu, 18 Sep 2014 08:10:33 GMT

Hi,

In my fact table I have several number fields that in my database are encoded as decimal(23,2). When I load them into my model, they are all interpreted and read as Money and formatted thereafter. I want them to be treated as the regular decimal numbers they are and be presented as such. I.e.: "95,85" not "kr 95,85".

Both my database and my Qlik-document have comma as decimal separator. My suspicion is that Qlik expects my fields are to be formatted as Money due to the two decimals. I do want to keep both decimals as a regular number, fixed to to 2 decimals.

Did anyone encounter this or have a suggestion to a better fix than manually setting the format of the fields either in script or document settings?

Some examples of numbers:

-281

-42,37

-38,7

0,24

1,17

95,85

Thanks!

Re: Decimals are treated as Money

PrashantSangle — Thu, 18 Sep 2014 08:16:01 GMT

Hi,

Use num()

or in UI

If you are taking it in List box then go to

List Box property->Number tab->override it to expected format.

Regards

Re: Decimals are treated as Money

MK_QSL — Thu, 18 Sep 2014 08:21:27 GMT

Load in the script as below

Num(Field,'#,##0.00') as Field

Re: Decimals are treated as Money

erikkorme — Thu, 18 Sep 2014 08:28:04 GMT

Hi,

Thanks for your replies. But I was asking if anyone has any ideas other than changing all problematic fields in the script, with the num() function, or changing them through document settings or item properties. I want Qlikview to somehow understand that these fields are not money, but regular numbers, without doing so explicitly. It is doable, but the script will be a lot less clean that way.

Thanks!

Re: Decimals are treated as Money

jagan — Thu, 18 Sep 2014 08:28:40 GMT

Hi,

Try like this

=Num(Num#(FieldName,'#,##0,00', ',') , '#,##0,00')

Ex:

=Num(Num#('95,85','#,##0,00', ',') , '#,##0,00')

Regards,

Jagan.