topic Re: Formatting trouble once again in QlikView

Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 09:39:33 GMT

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 ...

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 09:45:45 GMT

Have you tried with a numeric expansion:

$(# variablename )

Re: Formatting trouble once again

giakoum — Tue, 09 Jun 2015 09:46:59 GMT

did you try using the num# function?

Re: Formatting trouble once again

tresB — Tue, 09 Jun 2015 09:47:47 GMT

In the script:

SET DecimalSep='.';

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 09:49:26 GMT

Hi swuehl,

sure I have. I always need that in the script, no?

However, QlikView doesn't recognize the number as such, I guess it's because of the ',' which QlikView interprets as parameter_delimiter. Therefore I cannot even use the REPLACE() function.

When I use it from the start like

>> LET v_myvar = REPLACE(PEEK('fieldname'), ',', '.') <<

=> then the number reads like >> 103.500.37 << - it now has the '.' both as thousand_separator and as decimal_separator. I'm sure that won't work ...

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 09:52:44 GMT

Hi tresesco,

I will try that.

Actually, that SET command is on the "Main" tab and the comma is my default - so I don't quite understand why QlikView doesn't properly recognize the comma as decimal_separator?

I will try with num# also.

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 10:12:24 GMT

Do you really tried a numeric dollar sign expansion:

Let vA = num(1.5 ,'##0,00',',','.');

Let vB = num(2.3 ,'##0,00',',','.');

TRACE $(vA) / $(vB); // Would result in 1,50 / 2,30 ... error

TRACE $(#vA) / $(#vB); // Works for me: 1.5 / 2.3

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 10:13:35 GMT

Hi,

I am not quite sure how to use the num# function - the help_file is not particularly helpful in that respect.

Anyway, the variable is seemingly recognized fine - in an Inputbox I can display it as

>> 103.500,37 << (this is the variable v_myvar and it is numeric)

But I cannot divide that through another variable - the formula is like

>> num(($(v_myvar) / $(v_othervar)), '#.##0,00') <<

Then I get an error_message about the formula. That isn't particularly helpful, either - it always says ") expected" - the figures are both properly recognized judging from the log - but then it says "Error in expression - ')' expected"

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 10:17:54 GMT

Hi swuehl,

those are a bit too many commas - I don't get it.

I always use the num() function like

>> num([number], '#.##0,00') <<

My decimal_separator and thousand_separator are both set on the "Main" tab as ',' (decimal) and '.' (thousand). Why do I have to specify those again in the num() function? I have a complete formatting_code in there, no?

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 10:30:26 GMT

This is only to demonstrate the use of numeric dollar sign expansion on a formatted variable

The important part is

$(# vVariable)

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 11:26:27 GMT

Hi swuehl,

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

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 12:08:53 GMT

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.

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 12:18:03 GMT

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 😉

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 12:32:47 GMT

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.

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 12:43:54 GMT

Shouldn't you use LET here instead of SET?

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

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 12:48:31 GMT

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 ...

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 12:55:38 GMT

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 😉

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 13:05:29 GMT

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.

Re: Formatting trouble once again

datanibbler — Tue, 09 Jun 2015 13:11:30 GMT

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 ...

Re: Formatting trouble once again

swuehl — Tue, 09 Jun 2015 13:21:12 GMT

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 $(# )...