I have one source system that export the data with dot (.) as decimal seperator. All tables are extracted and stored into qvds. Other systems have comma (,) as decimal seperator, which is what's common and what I want to save my data with after transformation.
What happens is:
- even though I used SET DecimalSep=','; SET ThousandSep='.'; the app stores numbers with dot as decimal seperator
- opening qvd, clicking measure and viewing the columns alwasy shows dot as decimal seperator
- opening qvd through the script editor data source preview, sometimes gives me comma as decimal seperator and sometimes dot. Very unpredictable. the Num() seems to favor dot as seperator (but why?).
Questions:
- Why do Qlik Clouds file viewer and the script editor data source preview give different results?
- What is the preferred way to consistently change the number format of data into the format I want it?
Attached a screenshot from the load editor data preview and the data preview of a data file in the hub.
It's likely that the Qlik engine is trying to automatically format the number with a decimal point, but in some cases it fails.
Have you tried using NUM#() to explicitly parse the number?
NUM#(DecimalField, ‘.’, ‘,’) AS YourFieldToParse
Or you can use Replace() function:
Num#(Replace(YourFieldToParse, '.', ','), '#.##0,00') AS YourFieldParsed
The preferred method for consistently converting the numerical format of the data to the desired format is to convert it at the time of extraction. To give you an example, if the data appears with a decimal point in the original source (database), Qlik treats it as if it were a CHAR() type rather than a NUM() type; therefore, it is necessary to convert it at the outset, before loading it into the “.qvd” file, to ensure the data is already correctly formatted.
Let me know if this was helpful and if you need some help figuring out how to do it.
