Comma decimal separator saved as period in SaaS

[Mimiek]

Hi All, 

I'm using the Data Gateway Direct Access to upload my data via the ODBC connector to the cloud. 

In my data extract script I have this: 

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

But for some reason when I store the tables to QVD files in my data space my decimal separator becomes a period. 

Any idea?
Thank you!

[therealdees]

Are you just changing the value in the main script sheet? If so, try formatting the field using Num aswell:

Num(myField, '##.##0,00')

[Mimiek]

Hi @therealdees,

This was no issue when working on Qlik Sense on premise. 
It started when moving to the new Qlik SaaS.

I can do something like a Replace(MyField,'.',',') in the script, but I want to understand why I suddenly need to change all of my number fields after migrating to the SaaS. 

[Mimiek]

In attachment you can see chat is happening with the numbers. Quick fix is to change my DecimalSep to period instead of comma but that is not our standard notation in Belgium. 

SET DecimalSep='.'; 

[therealdees]

Hi, Mimiek

I've never used QlikView, but only SaaS. Unfortunately I'm not sure why this happens, but I had the same problem when consuming data from our ERP API.

I'm in Brazil and we write numbers like 1.000.000,00 (1 million), but the data comes in the opposite format (comma as thousand sep and period as decimals), so I keep ThousandSep = ',' and DecimalSep='.' in the extracting script, and later in the transformation script, where I clean the data, etc, I invert the separators and force the transformation using Num().

This worked for me, not really sure why. I agree with you, it should convert it in the first routine. Maybe it's something related to the way numbers come when consumed directly from an API, but I'm not sure.

If it's blocking your progress you could try the same to keep going, but I'm also curious to understand the reason behind this.

[Mimiek · Accepted solution]

Hi all, hi @therealdees,

It seems I have found a solution to my/our problem.
I personally work in 2 steps, step 1 data ingestion, step 2 data transformation.

In step 1 (app 1) I leave the thousand separator set to ',' and the decimal separator set to '.'. This way Qlik recognizes the decimals and also effectively stores them later as a 'number' (not as text). 

In step 2 (app 2) I set the thousand separator to '.' and my decimal separator set to ','. Because all decimals were effectively stored as one number in step 1, Qlik will 'convert' them perfectly.

So no need to use Num(), Num#(), Replace() on all numbers.

Hope this will help more people. 
Regards Peter

[therealdees]

Hi, @Mimiek 

Thanks for sharing!!

It confirms what I suspected, except I never tried not using Num() in my transformation script. It might save me some time from now on 🙂