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 swuehl,

what is that # for? I have never seen or used that before inside the DSE syntax.

[swuehl]

From the HELP:

Dollar-Sign Expansion Using a Variable

When using a variable for text replacement in the script or in an expression, the following syntax is used:

$( variablename )

$( variablename) expands to the value in variablename. If variablename does not exist the expansion will be the empty string.

For numeric variable expansions, the syntax $( variablename ) will generate a number using the regional decimal separator, i.e. for many countries a decimal comma. Such an expansion should not be used for numbers inside the script since these must use decimal point. Instead the expansion $(# variablename ) should be used. (Note the hash sign). It always yields a valid decimal-point representation of the numeric value of variablename, possibly with exponential notation (for very large/small numbers). If variablename does not exist or does not contain a numeric value, it will be expanded to 0 instead.

Example:

After execution of the following script:

SET DecimalSep=',';

LET X = 7/2;

$(X ) will expand to 3,5 while $(#X ) will expand to 3.5.

[datanibbler]

Aha.

Thanks! Somehow I never seem to find those parts of the integrated help which are really helpful ...

Only, in my case, that syntax yields something different:

My original variable is formatted like >> 103.500,12 <<.

That DSE syntax $(#v_myvar) yields >> 103500,12773388 << (or so; The point is, there still is a comma);

So that help_file and my reality don't quite meet ... What to do now?

Thanks for your help and patience 😉

[datanibbler]

I finally made it - well, nearly ...

I could now apply a Replace() function to that $(#v_myvar) by wrapping it in quotes, like

>> Set v_myvar2 = Replace('$(#v_myvar)', ',', '.') <<

That seems to work - but only halfway. Then the script actually finishes, but the line "Execution finished" never comes.

[swuehl]

Shouldn't you use LET here instead of SET?

LET v_myvar2 = Replace('$(#v_myvar)', ',', '.');

[datanibbler]

Hi swuehl,

yes, of course. But I did that, just wrote it wrong here. Sorry. It is LET in the script.

with that Replace() like I just told you it seems QlilView now regards that as a string and cannot use it for calculating.

I'm also trying around with the divisor. That is an integer, no decimals, so there is not much to do with that.

Let's see ...

[datanibbler]

OK.

It's just like I thought, the variable with the complicated formatting is not numeric anymore after I have applied that Replace() function.

Maybe I can use the EVALUATE() function? Or num# now?

If it wasn't so not_funny, I'd say this is ridiculous 😉

[datanibbler]

Ho!

It seems I've finally hit home by chance - by using the evaluate() function, the variable is finally numeric again ... I'll try whether the calculation works now.

[datanibbler]

Hmmm ... doesn't seem to work.

I cannot imagine why - both values are numeric now, I've tested them with the ISNUM() function on the GUI. So dividing the one through the other should not be a problem ...

[swuehl]

I must admit I'm also getting confused, especially with the $(# vVariable) not working as expected.

Maybe hic‌ could shed some light on this issue.

I've re-read his blog post about quoting:

QlikView Quoteology

And using double quotes to reference the variables in the script seem to work:

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

TEST:
LOAD num(F1) as F1, num(F2) as F2 INLINE [
F1, F2
10.75, 3.3359
];

Let vA = Peek('F1',0,'TEST');
Let vB = Peek('F2',0,'TEST');

Let vC = "vA" / "vB" +1;

Let vD = "vC" /2;

Maybe double quotes are the new $(# )...