Formatting trouble once again

[datanibbler]

Hi,

I have some trouble again with numeric variables.

I have to divide one variable through another (to get an average of costs).

The issue is, in the table where one of these variables comes from (using the PEEK() function) I have formatted that in the German format with '#.##0,00'.

<=> Internally I guess QlikView uses the '.' as decimal_separator. what I know is that dividing this through another variable (an integer) does not work.

=> I tried to replace the ',' with the '.' to make it usable, but I don't quite know how to do that in the script - it doesn't work yet. I guess QlikView misinterprets the ',' as a parameter_separator. On the GUI, it's no issue at all to divide the one through the other, but in the script ...

Can someone help me there?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I'm just trying reformatting the field with the '.' as decimal_separator, that might work - but the variable is also used in another calculation and I have to see if that still works ...

[datanibbler]

Hi,

very strange. Well, I'll go on trying while hoping that someone else might be able to help us.

I have now made another variable v_calc which holds the division I need. That, too, is numeric - but when I let the final LOAD run, which encompasses that variable - I use DSE like I need to in the script with variables, but without the # - I actually see the line "Execution finished" in the Debugger - but there everything stops and freezes until I eventually stop that task ...

[datanibbler]

Aha - now I've commented out that line from the final LOAD and QlikView again freezes on that - so there seems to be an error in one of the other fields.

Let's see ...

[hic]

Stefan

We have had some bugs in the $(#...) expansion, and I thought they were all fixed... but I now see that this is not the case.

I tried the following

     SET ThousandSep=' ';

     SET DecimalSep=',';

     Let vNum = Num(103500 + 12/100,'#.##0,00', ',', '.');

     Let vText = Text('$(#vNum)');

and it works just fine. But when I change the ThousandSep in the first statement from space to point '.', the numeric expansion in the fourth statement breaks.

It's a bug. I'll report it.

HIC

[datanibbler]

Hi Henric,

in the meantime, do you have any advice for me to finally fix my problem with a "simple" division?`

I'll just try commenting out all the other fields from the LOAD (I have an * there) to make sure whether the problem is still with that division or in some other field.

I'll be back instantly.

P.S.: Well, it really seems that this is fixed and I have a problem somewhere else.

The number contains a comma so that QlikView, in a LOAD, interprets it as two fields to load instead of one, so I need to wrap it in quotes, but the table_viewer tells me it is still numeric.

I will see about all the other fields now.

Than, you both!

[swuehl · Accepted solution]

Thanks for the update and fast response, Henric, I already got some grey hair over that.

Regarding the double quoting (which I've just recently learned), this seems to work out nicely (in the script), are there any issues known with that method?

[hic]

Use blank as default thousand separator, and everything will work.

HIC

[datanibbler]

The double    quoting, when applied to the variable, didn't seemingly change anything about it. It still had the separators like I set them on the Main tab. I then used two nested REPLACE() functions to remove the thousand_separator and set the decimal_separator as a comma and the evaluate() function then made it numeric again.

Thanks a lot for your help! I hope I'll get the rest fixed. Let's see ...

[hic]

I wish I could say no... ... but the truth is that there are still some untested combinations and things that don't work. (that I consider to be bugs), for example

• Doubling the grave accent will not work.
  Example: A field xy`z cannot be loaded using `xy``z`.
  Work-around: Use square brackets [...] or double quotes "...".
• If the first character is a quotation mark, you cannot escape it.
  Example: A field "Name"5 cannot be loaded using """Name""5".
  Work-around: Use a delimiter that is different from the first character, in this case square brackets.

So, generally I would say that most things work, but you should still be careful and test whatever you do.

HIC

[datanibbler]

Hi Henric,

one more question: It seems like my problem with that division is finally fixed - but some of the other fields in that LOAD proves troublesome. Is there any best_practice to find out which one? I'm just working on getting all the field_names in a list and putting them explicit in the LOAD instead of the *. Then I might try using a FIRST 100 or so.

Is there anything else I should have in mind for this?

Thanks a lot!

Best regards,

DataNibbler

[hic]

No, I think that that is the right approach: First create a load with explicit field names - no wildcard. Then put a First 10 in front of it. If that works, you know the syntax is OK.

Then increase "First 10" or remove it completely. At that stage, you might encounter semantic problems - problems caused by the data not being what you think it is. And then you might need to dig into the data to find inconsistencies. The Text() function is your friend here. With it you will see the value without any numeric interpretation.

For fields that you expect to be numbers, you should check that they are right-aligned in a list box. If not, they have not been interpreted correctly.

HIC