SAP SQL Connector issue

[Anonymous]

Hello.

I've faced with 2 problems using SAP SQL Connector (6.5):

1. If first symbol in SAP table is space symbol (' ') then I receive field data with missing first space symbol, and as result I have a wrong data in QlikView (11.20). I've tried many combinations of connector advanced properties but still have the same result every time. What can I do in that case?

2. I'm receiving error "The value '0508201407:59:03 ' could not be converted to type double" while extracting fields which contains dates and have data type of DEC. I guess that connector is expect DEC data type but SAP for some reason transfers to connector string representation of date and connector is failed.

How I can solve this problems?

[Peter_Cammaert]

Are you sure that the SAP Connector is the culprit?

AFAIK by default QlikView trims values it loads from Files/Connectors unless they are treated as text(), which is most probably also the reason why you get the second error (this is not a recognizable date, and you better convert it by importing it as pure text and use timestamp#() to convert it into a regular datetime dual.)

Yes, it's true, a lot has changed in the way that the SAP connector converts/refuses to convert values, thereby leaving it to QlikView to do what is necesssary. If you don't want those changes, you can try switching to legacy mode but I don't think that'll fix everything.

[Anonymous]

Thank you for your reply!

There are two different case with different tables:

1 Trimming space symbol:
I am not sure who is culprit in that case but I don't know how to fix that problem. This missing symbol is important because that SAP table stores information about contractors and that stored text is divided by 4 fields. I need to combine this 4 fields to get full string with the name of contractor.
Example:
Case 1: Field1: "Boring", Field2: " company"...
Case 2: Field1: "Alek", Field2: "sandr"...
Expected result if I'll combine these fields in QlikView is "Boring company" but I get "Boringcompany" but the result for second case is correct.
If it were common SQL database like MySQL it would be possible to fix this on database side by different ways such as "SELECT Field1 & Field2 as Field ... FROM table" and other. Is there a way to use SQL functions with SAP SQL connector?

2 String to double conversion:
About second case I'm sure that it's problem on connector side because I see it in connector logs (I can attach a log file). Connector can't convert that string value to double, not QlikView. And Connector expects from SAP the DEC data type for that field but receives the string in unexpected format and raises an exception. As result I can't receive this data in any format (string, DEC, double, date or something else) and QV reload is aborted. So I can't use QV script functions because of error during reload process.
Any ideas? Is there a way to define data types in connector? About legacy mode - did you mean FLTPlegacy = true and TIMSlegacy = true properties of sap sql connector?

[Hakan_Ronningberg]

Hi,

It is not possible to concatenate fields in the SQL statement using the SQL connector. I guess that has to be done in the script.

Regarding the problem with the decimal value. In older releases of the connector, a Timestamp (stored in SAP as data type DEC, length 15) could be sent to Qlik formatted like '2018-11-30 10:04:00' if the Connection String Property 'ConvRoutine' was set to true. However in release 6.5 values with data type DEC are always sent to Qlik as not formatted. So it should not cause any problem. Maybe you are using older SAP transports together with the 6.5 Windows part of the connector?
Otherwise, please attach the complete log file.

Regards,
Hakan

[Anonymous]

Hello!

Concatenating fields in QlikView will cause this problem: "Boringcompany" (expected: "Boring company") because the space symbol is trimmed by connector or QlikView. I have no idea how to fix that. Is there a way to disable auto trimming?

About decimal value: ConvRoutine is set to false, but  I've tried to set it to true too and to change/combine other properties. Extracting data with that field (CREATE_TIME in log file) always failed.

Log file in attachment.

[Hakan_Ronningberg]

Hi again!

I can see in the log file that you are using the SAP transports of release 6.1.1. The decimal problem can be caused by that. I suggest that you upgrade both the SAP transports and the Windows parts of the connectors to the latest release 7.0.1.

To avoid the trimming of leading space, put this in the QlikView script:

set Verbatim = 1;

Regards,

Hakan

[Anonymous]

Hello, Hakan!

Thank you for help, especially with system var "Verbatim". It's good to learn something new :). Now fields are loading into QlikView without trimming but there is another problem with them now:
These fields in SAP table has 40 char length. Problem occurs when the text value in field is less then 40 symbols. In that case "unused" length of field is filled with space symbols up to max length.

Example:
In SAP field1 (40 char len):
value: "Boring " (7 symbols of text)

In QlikView field1:
value: "Boring " (40 symbols of text included 7 original text values from SAP)

I can't just use RTrim() or another functions to delete this space symbols because it will cause another problem for some contractors:
Field1 & Field2 = "Boringcompany"
In SAP these text values are correct.

As you advised I'll update SAP transports and connector first, hope it'll helps.
I'll let you know if the problem persists.

Thank you!

[Peter_Cammaert]

This remark won't help you right now, but may be useful to consider in the future.

If you want to make sure that all data you pull into QlikView/Qlik Sense gets properly displayed, whatever the connection or source, then clean & format all fields in your script. In your case, do not rely on SAP adding enough whitespace to separate name fields, but use something like

Trim(FirstName) & ' ' & Trim(LastName) AS FullName

I don't know where or when I got this habit, but I do it automatically when creating new script code, even when the data clearly looks ... clean. Just to be safe. You know, you can't trust your source systems, or your source system users for that matter.

If you are worried about performance, then don't be. These functions in LOAD scripts are unbelievably quick.

[Anonymous]

Hi!

Yeah, you're right and I'm already using this way in other QV apps, thank you. But this won't help me in my case.