Issue with SAP BW connector with Qlikview: text read as numeric

[Anonymous]

Hello

I am using Qlikview 11.20 and the SAP BW connector to extract data from a BW table. It work most of the time, but I have funny behaviors some time;

Here is the query:

[/BIC/TZC_MAT]: 

Load

  [/BIC/ZC_MAT] as ZC_MAT,

  [TXTMD] as TXT;

SQL Select /BIC/ZC_MAT TXTMD from /BIC/TZC_MAT;

this query is working, but some values in ZC_MAT are incorrectly fetched. This field is a product number and it does not follow any rule. i could be made of letter, sign number or all of it.

And I have a problem with the following values:

01246889  fetched as 1246889   >>> I can fix it using Text([/BIC/ZC_MAT]) as ZC_MAT,

217-  fetched as -217  >>> I cannot find a solution.  Seems Qlikview sees it a negative numeric

The worse if that I have another material with code -217 so I cannot differentiate them in Qlikview

Any idea how I could resolve this issue?

I tried a lot of things already.

Thank you

[Anonymous]

Do you have a Conversion Routine applied for ZC_MAT in BW ?

If so, are you applying it when extracting the data using the SQL Connector ? (ConvRoutine = 0/1 in the Connection String )

Thanks

Yaniv

[Anonymous]

Thank you Yaniv

I tried this parameter in the connection string. It did not change anything.

The field ZC_MAt in BW is a Char 18

The values can be anything. Here is a sample

217-

145-87

001245897

PRI78915

451387

And the issue is "217"- become  -"217" when it is fetched in Qlikview

Hope this will provide more hint on the issue.

Regards

Cyril

[Hakan_Ronningberg · Accepted solution]

Hi Cyril,

You don't say which connetor release you are using. However in release 6.1.1 there was a change that probably will fix your problem. Before that release character strings containing only digits (and '-' sign') were regarded as numeric and the '-' sign was moved to the left.

From the Release notes for 6.1.1:

The SAP data types CHAR (character) and NUMC (numeric character) were previously interpreted (in the Qlik script) as ‘numeric’ if they contained only digits. Leading zeroes were removed. (0000141000 became 141000).
Now they are always interpreted as ‘text’. Leading zeroes are kept. These data types are not often used in calculations, but if they are the new behavior might require script changes since calculations expects numeric values
Please consult the QlikView/Sense documentation on how to work with number and text formatting in QlikView/Sense. http://help.qlik.com/

Regards,

Hakan

[Anonymous]

Thank you

That's exactly my problem.

I will install this version and check my scripts.

Best regards

Cyril