Perform maths division in load script

[robertmlawton]

I am trying to perform some simple division in a load script but its not working:

TEST:
Load
     Value1/1.2 AS Value2
From [lib://DATA/SomeData.qvd](qvd);

 The server I am on working on is in Spain and their default numeric separators are ',' for decimal and '.' for thousands.

The qvd being loaded in in UK format ( '.' for decimal and ',' for thousands) and i want the output in UK format so in my load scripts i set the defaults to be UK format '.' for decimal and ',' for thousands. But when i do this the Value2 field is blank.

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

If i set the separators to the default Spanish setting and replace the decimal with a comma in the script it works, even though the 1.2 is in UK format:

TEST:
Load
     REPLACE(Value1,'.',',')/1.2 AS Value2
From [lib://DATA/SomeData.qvd](qvd);

How can I use UK formatting and get everything to work?

Many thanks in advance

Rob

[ToniKautto]

It is accurate to change the format variables to your desired app format, as it makes it easier to generalize the script. The only missing part is to format the loaded data so that it always follows your desired format.

Replace is a string function, which makes the result a text without numerical representation. Unless you are working with actual text values, try using interpretation functions like Num#() and/or Num() to change the look and feel of  loaded data. 

https://help.qlik.com/en-US/sense/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationFunctions...

Num#() enables you to interpret the loaded values as a text and convert it to a number;

Num#(Value1, '#.##0,0', ',', '.')

Notice, Qlik uses dual values, which means that the value will still look like your Spanish format, while it now has a underlying accurate number that enables calculations. Num() enables you to interpret a number and convert it to look like your preferred format. In this case you want to apply the format according to your script variables; 

Num(Num#(Value1, '#.##0,0', ',', '.'))

If you have mixed values loaded in the same load statement, you can consider using Alt() as a way to apply the first format that matches your loaded data. This way the statement can handle any defined format regardless of how you alter the script variables;

Alt(Num(Num#(Value1, '#.##0,0', ',', '.')), Num(Num#(Value1, '#,##0.0', '.', ',')))