SAP SQL connector properties does not work

[andy]

Hi,

Using a Qlik Sense Server April 2018 release and the SAP connector is version 6.6.

(Nothing about it in release notes of version 7)

Found out that a lot of fields in the SAP-QVDs I'm fetching data from holds just a single space which gives different behavior now in Qlik Sense than we had with Qlikview. In my old Qvds these spaces where '' (blanks)

Found after searching that there is a property on the SQL-connector which should solve the problem.

ReplaceNullvalue

0/1 (default/on = 1)

If on, replaces all ’null’ values in the data with ‘ ‘ (SPACE) before sending it to Qlik Sense.

but it doesn't work as expected as can be seen below where a field in table AFVC still contains only one single space instead of being NULL.

I don't have any SAP-GUI so cannot confirm that they haven't filled all fields with single spaces so I tried another property so see if that worked.

I also this property

MixedMode

0/1 (default/off = 0 or false, on = 1 or true)

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).

and expected the field below to be a numeric without preceding zeroes.

Anyone who can explain how these properties should work?

Regards

Andreas

[Hakan_Ronningberg]

Hi Andreas,

Maybe you should use this connection string property?

RemoveAllBlanks 0/1 (default/off = 0) If on, provides the possibility to get the behavior where all blanks in fields in SAP containing only blank characters are trimmed. The default behavior leaves one blank character in order to differentiate from NULL fields.

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Clients/Using-SAP-SQL-...

Regards,

Hakan

[andy]

Hi,

Tried that but no change at all, seems like the properties does not work.

/Andreas

[Tomas_Sommergyll]

Hi,

Would it be possible to see the script with the properties added?

Br,

Tomas

[andy]

Hi,

This is the properties

This is the script:

LIB CONNECT TO 'SAP_SQL_TEST';

[AFVC]:

First 10

LOAD

BEDZL,

[/ISDFPS/OBJNR];

SELECT

BEDZL,

/ISDFPS/OBJNR 

FROM AFVC;

TRACE Store into $(vG.ExtractSAPPath)\AFVC_new.qvd;

STORE AFVC INTO $(vG.ExtractSAPPath)\AFVC_new.qvd (qvd);

//DROP TABLE [AFVC];

The stored qvd-file is attached.

[Tomas_Sommergyll · Accepted solution]

Hi,

Thanks for the attachment. I looked at the provided qvd file and I can see the numbers are indeed changed from text to dual, so the MixedMode property is working. As for the formatting of leading zeroes, it is Qlik Sense that is formatting these and by writing e.g. Num() on the fields in the script, e.g. Num(BEDZL), the leading zeroes will disappear.

As for the null values vs 1 blank space, I have used both version 6.6 and 7.0 on our internal test tables with the same result: if the ReplaceNullValue=false, the null value is present in the qvd, otherwise it is one blank space. My guess is that there is a blank space in your table already and changing the property will make no difference. If you investigate your table and is certain that it contains null values, then I suggest you contact the Qlik Support for further help.

Edit: I also tried the RemoveAllBlanks=true and the field that previously had 1 blank space, had now become <null>. If both RemoveAllBlanks=true and ReplaceNullValue=true the result is still the same: <null>.

Thanks,

Tomas

[andy]

Thanks Thomas for your thorough test on another SAP system.

I've now asked the SAP-people if they have introduced a single space in the data when transferring data from one SAP to another.

They confirm that the space is in the table...

I just couldn't believe the facts that Qlik showed me.

Best regards

Andreas