Trouble with decimal settings ... again

[datanibbler]

Hi,

I have a value that I have calculated in my script as >> 24,93051 <<.

Script settings are:

>>>>>>>>>>>>>>

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';

<<<<<<<<<<<<<<<<<<<<<

So the comma should be used as decimal_separator - I know that QlikView calculates with the opposed settings internally. Right?

So when I type

>>  round([that value], '0.0001' <<

should not that round the output to four decimals?

Well, it doesn't. Instead this gives me 0.0025 or something.

So what do I have to do to get this value rounded to four decimals?

Thanks a lot!

Best regards,

DataNibbler

[avinashelite]

Hi DataNibbler,

Really sorry, I didn't observe you words.

To Make it simple.

NUM() function  converts only numbers to desired for according to our requirement , if your input is a text format number then num() will not help us in formatting the same. NUM() works only on numbers.

NUM#()

If it is numbers that are expressed as text, you can use the num# function

to make sure that they are seen as numbers, It works on string to number conversion ,The # functions will return

the original value as the text portion of the dual.

The # functions apply a format string to the incoming value. If it parses correctly as number

then it loads the value as a number.

Hope this helps you !!

[datanibbler]

THi Avinash,

well, this helps me in a way - yet I'm no further.

So num# will actually only be the correct function to use if what I have is a TEXT, right?

Well, what I get from Excel is already numeric, only it has the "comma" as decimal_separator which causes nothing but trouble lateron. I will tell you exactly what I have:

(I have already posted what my script settings are on the "Main" tab. Essentially, the "comma" is set as the decimal_separator to use)

LET v_MinSatz_syncreon_pre = PEEK('Minutensatz_€');

// That comes from the Excel_file (in Excel formatted as numeric with four decimals, it has the , as decimal_separator),

// the variable now reads >> 0,4017 <<

// It is numeric all right (tested with ISNUM())

Now I guess I have to turn that around - for somewhat later in the script, I have another value (without decimals)

to be multiplied with that, and the output has some decimals (I make it four using num()).

=> That second value is numeric all right and also has the "comma" as decimal_separator.

<=> Still later, I compare that to a third value which also has the "comma" as decimal_separator and four decimals and actually looks identical - but the IF_query tells me they are not equal.

Thanks for helping! I really don't want to spend another day messing around with this ...

[avinashelite]

Do one thing, convert all your number to single format in the script i.e with or with out comm all to one format ..then perform the calculation on top of this .

[datanibbler]

Hi Avinash,

I just looked over my script.

Apart from this, there are no specific formatting functions applied to the data - I load it like it is in Excel. All the values have the "comma" as decimal_separator, as it is set up on the "Main" tab of my script. It is actually all quite uniform.

That's what confuses me - I just don't understand why at the end QlikView still tells me that those two identical-looking values (both numeric, with four decimals and the "comma" as separator) are not equal.

I will try using a formatting (the num() function) on every one of those values. Maybe that helps ...

[datanibbler]

This is really driving me nuts.

It seems that when I leave the variable as it is - with the "comma" as decimal_separator, then as soon as I try to multiply another value with that in my LOAD, QlikView produces an error because that "comma" is misinterpreted. So I have to somehow turn that around.

Maybe it would work if I take two steps - first I apply a REPLACE - that works, but the outcome is not numeric anymore - and then I use num# to make it numeric, specifying how I want it to be formatted.

[sasiparupudi1]

Please close this thread if you have got your answer

[datanibbler]

Hi Sasidhar,

I will.  But the issue is not yet solved - I'm still discussing with Avinash, but that is further up - the different options of replying make this a bit confusing.

I think I have now understood that num# is a function that is only applicable when I have a value formatted as TEXT. Right? I get a numeric value from Excel all right, it just has the "comma" and that is causing trouble, so I need to swap it - so I use REPLACE() which converts it to TEXT and then I use num# (with four parameters) to make it into a numeric value like

>>  0.4017  <<

[sasiparupudi1]

Yes

Num# can convert a given string to a number based on the given format code. if the number format does not match with the format code then the input string is returned back else a number is returned.

Num can format a number based on the given format code

The format code that is used really dependent on your requirement..If nothing is supplied, it uses the operating system default format.

[datanibbler · Accepted solution]

Hi Avinash,

finally I think I have made it - though I don't quite understand.

I had a few variables, all numeric, all with the "comma" and a few values from tables to multiply with those, all numeric, all with the "comma" - still, at the end of it, I had two identical-looking values, but the IF_query told me they were not equal.

Now I did the same to the final values once again - I used first the Replace() (to swap the "comma" with the "dot") and then num# to get a valid numeric value again - and now the query tells me they are equal.

Eventually I have to find out what is the root cause of this strange happening - else I will have the same kind of trouble again and again. It must be connected to the fact that the basic settings in Excel are in German formatting - the "comma" as separator - I cannot change those, I have no admin_rights on my machine, so I have mirrored those in the SET commands in my script, but the internal calculations of QlikView are done the other way round.

Well, I guess this is solved. Thank you all!

[avinashelite]

Cool!! its the common issues with excel files, glad you resolved it