1 Reply Latest reply: Oct 25, 2017 7:57 AM by Helga Wandel RSS

    Numbers from Data Market interpreted as Strings

    Helga Wandel

      I am running Qlik Sense June 2017 release. I want to use the free 3x3 currency exchange rates from Qlik Data Market.

      I have a German Windows 10 with German regional settings, thus the decimal point is the comma "," and the thousand separator is the dot ("."). The Main section of my data load script reflect this:

       

      SET ThousandSep='.';

      SET DecimalSep=',';

      SET MoneyThousandSep='.';

      SET MoneyDecimalSep=',';

       

      When I add the 3x3 currency exchange rates to my data model, the currency exchange rate is being interpreted as Strings (see table screenshot below). The exchange rates are displayed with dots as the decimal point, while my other numbers are being displayed with a comma as the decimal point. Thus any measure calculation using these exchange rates result in 0 because exchange rate is not being interpreted as a number (see screenshot attached).

       

      It works when I switch to my Windows to English regional settings and change the global variables in my data load script accordingly.

       

      Any idea how to get this working with German regional settings?

       

      Thanks in advance

        • Re: Numbers from Data Market interpreted as Strings
          Helga Wandel

          I contacted support about this issue and the anwer was that the Data Market currently does not support localized number formats. Thus currency exchange rates received from Data Market will always be in English format, i.e. using the dot as the decimal separator and the comma as the thousand separator.

           

          There are three possible workarounds:

          1. Manually change the settings in the main part of the data load script to English settings.

          2. In the formula that uses the exchange rate from data market use the Num# function to convert the exchange rate to the desired number format, e.g.

          Num#([Exchange rate],'#.#', '.' , ',')

          3. Use a calculated dimension in the data load script that does the conversion using the Num# function, e.g.

          LOAD

          ...

          Num#([Exchange rate],'#.#', '.' , ',') AS ExchangeRate_German;

          ...