SAP Connector returns wrong values

[tabletuner]

Hi all,

Recently i read a table from SAP called ADRC with the QV Sap Connector for SAP x64 version 6.0.124. ADRC is the address table. It contains a field called 'HOUSE_NUM1'.

This field is of datatype CHAR(10).

The field contains all housenumbers of the customers.

Below are two simplified scenarios of how the connector behaves.

Scenario  1)

Field values in the source table in SAP:

HOUSE_NUM1

1

2

2E

When the table is read by the SAP Connector this way, it will give the following output:

HOUSE_NUM1

1

2

2

Scenario 2)

Field values in the source table in SAP:

HOUSE_NUM1

2E

2

1

When the table is read by the SAP Connector this way, it will give the following output:

HOUSE_NUM1

2E

2E

1

I conclude that 

- QlikView somehow determines that if an integer value is the first read field value (e.g. 2) then all subsequent records for this field with string values starting with the same integer (e.g. 2E) are converted to this integer (2). 

- QlikView somehow determines that if a string value is the first read field value (e.g. 2E) then all subsequent records for this field with integer values starting with the same integer (e.g. 2) are replaced by the string value (2E). 

Of course i dont want the connector to automatically manipulate my data. So does anyone know what setting I must change to my ConnectionString in order to prevent this from happening? Below my connectionstring:

CUSTOM CONNECT TO "Provider=QvSAPConnector.dll;ASHOST=***;SYSNR=PRD;CLIENT=300;KeepCasing=1;TimeOutFetch=5000;NullDate=0;ReplaceNullvalue=0;RemoveAllBlanks=1;XUserId=***;XPassword=***;";

Regards,

Tjeerd

[marcus_sommer · Accepted solution]

I'm not sure if this is really related to the connector respectively the settings of the connector else this kind of misinterpretation happens by a other sources, too. The solution is usually to force Qlik to load the field as string - often applied within a preceding load, like:

load text(Field) as Field;
sql select Field from db;

- Marcus

[tabletuner]

Thanks a lot Marcus. This solution solved my issue. However, i still dont understand why it happens. And im concerned what impact it could have on all other char fields that im reading form the SAP source system.

[marcus_sommer]

Qlik has no data-types in the sense they are common within most databases or many programming tools else the data will be interpreted - to numeric or to strings and in some cases to a mix of both. In many scenarios this isn't  bad (because your noticed behaviour didn't happens or it's so seldom that the measures to force an appropriate interpretation aren't significant) else it's part of the performance benefit from Qlik by avoiding some abstraction layer during the processing of the data.

If you just know this special feature you will notice it very early in the development and you could add this simple text() to the few related fields. Nevertheless you could also apply the following suggestion - maybe even within a global solution:

Touchless Formatting | Qlikview Cookbook

- Marcus

[Hakan_Ronningberg]

Hi, 

I can see that you are using an old release of the connector. In release 6.3.2 there was a new connection string property added:
MixedMode (0 or false / 1 or true. Default/off = 0 or false.) When MixedMode is enabled, the NUMC and CHAR data types are sent as UNKNOWN instead of ASCII, and it is up to Qlik to decide what their content is. A numeric string with a maximum length of 14 characters will be interpreted as numeric in Qlik (the same behavior as in the legacy connector).

Not sure if this will fix your problem, but might be worth a try!

We always recommend to upgrade to the latest version! Attached is a document where you can see a summary of improvements and bug fixes!

Regards,
Håkan