Different data type in cloud versus on prem

[MichielHofsteenge]

I’ve got a big issue. Unfortunately, this customer has used almost no ETL, which is why I’m looking for a better solution than using a replace everywhere. The situation is as follows:

On-prem, the customer has a Progress OpenEdge 32-bit ODBC connection. For a large number of fields, the data type is numeric. On-prem, Qlik automatically converts the numeric values to a comma according to my settings.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ #.##0,00-';

Unfortunately, when I use a gateway, I don’t get a data type back and Qlik shows the value with a . instead of a , . For the gateway, I’m using a newer driver and 64-bit instead of 32-bit. The SQL syntax is set to SAP HANA SQL syntax, because that's the only one that works.

Does anyone have any idea how to solve this?

[marcus_sommer]

If I understand it right, the cloud has the correct numbers and only the formatting is different by switching dot and comma as decimal-delimiter?

[MichielHofsteenge]

Hi Marcus, 

Thanks for your reply. What you’re suggesting seems correct, although I’m not 100% sure whether the database is using a dot or a comma. What I did notice is that I receive a tag on-prem, but I don’t have that in the cloud. See the screenshots below.

[FedericoDellAcqua]

Have you tried to force the numeric format in cloud? 

[marcus_sommer]

If there were no essential changes for the cloud compared to the on-prem versions in regard how data are interpreted and stored - the tags are not really relevant else a kind of extra information. Because Qlik has no concept of data-types else the loaded data will be interpreted in regard to the applied interpretation-variables - into numbers or strings respectively mixed fields.

If you get numbers where you expect ones there is no real issue else just the formatting might be different - and this could be adjusted.

[MichielHofsteenge]

That’s the issue here. Qlik Cloud interprets it as text instead of a numeric value. Because of that, I can’t force it to display in the correct format using either the settings or the num function. A replace does of course work in this case. But since the customer hasn’t used ETL, I’m looking for another solution than having to apply a replace for every value. That would cost me a significant amount of time to go through more than 50 apps or rebuild the structure.

[marcus_sommer · Accepted solution]

Is any interpretation applied respectively adjusted? Maybe from:

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

to:

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

[MichielHofsteenge]

Yes, now it's the other way around. 

[MichielHofsteenge]

Ah thanks @marcus_sommer. Sometimes the solution is so simple that you just dont think of it. 

[SterreKapteijns]

I've had many issues as well with changing field formats when using the Data Gateway. Still don't know why 🙂