Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Thank you
That's exactly my problem.
I will install this version and check my scripts.
Best regards
Cyril