How to load fields with same name from tables in join since SAP Connector does not support 'AS' in fields?

[diagonjope]

Greetings!

I am trying to issue the following query using the SAP Connector

SELECT

    VK~VBELN,  // Billing Document Number

    VK~FKART,  // Billing Type

    VK~VKORG,  // Sales Organization

    VK~FKDAT,  // Billing date for billing index and printout

    VK~NETWR AS BILL_TOTAL,  // Total Value in Document Currency

    VK~XBLNR,  // Reference Document Number

    VK~MWSBK,  // Tax amount in document currency

    VP~MEINS,  // Base Unit of Measure

    VP~FKLMG,  // Billing quantity in stockkeeping unit

    VP~NETWR,  // Total ammount per line item

    VP~MATNR,  // Material Number

    VP~ARKTX,  // Short text for sales order item

    VP~MATKL,  // Material Group

    VP~WERKS,  // Plant or Center

    VP~WAVWR,  // Cost in document currency

    VP~KZWI1,  // Subtotal 1 from pricing procedure for condition

    VP~KZWI3,  // Subtotal 3 from pricing procedure for condition

    VP~MWSBP   // Tax amount in document currency

FROM VBRK as VK left outer join VBRP as VP ON VK~VBELN = VP~VBELN

where VK~FKDAT >= '20160101'

                 and VK~FKART = 'X'

                 and (VK~VKORG = 'Y' or VK~VKORG = 'Z');

Apparently the Qlik SAP Connector does not support 'AS' in fields, so the query is failing with the following msg:

QVX_UNEXPECTED_END_OF_DATA:

The collection has been marked as complete with regards to additions.

The query works as expected as soon as I comment out the 'VK~NETWR AS BILL_TOTAL' line, but I need to retrieve the contents of the NETWR field in both VBRK and VBRP.  The query also fails if I leave VK~NETWR and VP~NETWR in the Select (apparently the connector is ignoring the name of the table as it constructs the resulting data set for the join).

We are using Qlik Sense Enterprise April 2018 with the Qlik SAP Connector 6.6.0+Build:00257.

Any suggestions on how to retrieve both fields needed?

Cheers,

--José

                ;

[vishsaggi]

Can you try this?

LOAD *,

     VK~NETWR AS BILL_TOTAL;

SELECT

    VK~VBELN,  // Billing Document Number

    VK~FKART,  // Billing Type

    VK~VKORG,  // Sales Organization

    VK~FKDAT,  // Billing date for billing index and printout

    VK~NETWR   // Total Value in Document Currency

    VK~XBLNR,  // Reference Document Number

    VK~MWSBK,  // Tax amount in document currency

    VP~MEINS,  // Base Unit of Measure

    VP~FKLMG,  // Billing quantity in stockkeeping unit

    VP~NETWR,  // Total ammount per line item

    VP~MATNR,  // Material Number

    VP~ARKTX,  // Short text for sales order item

    VP~MATKL,  // Material Group

    VP~WERKS,  // Plant or Center

    VP~WAVWR,  // Cost in document currency

    VP~KZWI1,  // Subtotal 1 from pricing procedure for condition

    VP~KZWI3,  // Subtotal 3 from pricing procedure for condition

    VP~MWSBP   // Tax amount in document currency

FROM VBRK as VK left outer join VBRP as VP ON VK~VBELN = VP~VBELN

where VK~FKDAT >= '20160101'

                 and VK~FKART = 'X'

                 and (VK~VKORG = 'Y' or VK~VKORG = 'Z');

Concatenate

SELECT

    VK~VBELN,  // Billing Document Number

    VK~FKART,  // Billing Type

    VK~VKORG,  // Sales Organization

    VK~FKDAT,  // Billing date for billing index and printout

    // Total Value in Document Currency

    VK~XBLNR,  // Reference Document Number

    VK~MWSBK,  // Tax amount in document currency

    VP~MEINS,  // Base Unit of Measure

    VP~FKLMG,  // Billing quantity in stockkeeping unit

    VP~NETWR,  // Total ammount per line item

    VP~MATNR,  // Material Number

    VP~ARKTX,  // Short text for sales order item

    VP~MATKL,  // Material Group

    VP~WERKS,  // Plant or Center

    VP~WAVWR,  // Cost in document currency

    VP~KZWI1,  // Subtotal 1 from pricing procedure for condition

    VP~KZWI3,  // Subtotal 3 from pricing procedure for condition

    VP~MWSBP   // Tax amount in document currency

FROM VBRK as VK left outer join VBRP as VP ON VK~VBELN = VP~VBELN

where VK~FKDAT >= '20160101'

                 and VK~FKART = 'X'

                 and (VK~VKORG = 'Y' or VK~VKORG = 'Z');

[diagonjope]

Thank you Vishwarath for your response.

I guess that a more accurate tittle for my question would have been: since the SAP Connector is SAP certified, why isn't this Open SQL statement working as expected?

I don't want to concatenate the results of two separate queries for something that should be handled in just one: in this case we're talking about 500+ million records in each query. 

At the Qlik data model level, I could've also separately loaded the header table (VBRK) as a mapping table, containing just the billing document number and the bill_total fields, and then use it as part of an applymap() to add the NETWR to the table that contains all the other joined fields that I need.  Again, a Qlik customer shouldn't have to do all these maneuvers to achieve something that SAP Open SQL allows as part of its syntax ==> SELECT - select_list - ABAP Keyword Documentation

BTW, I would have to try it in this case since it is a different version of the Connector, but something else I discovered recently, is that the 6.2 SAP Connector would give you a "Field not found" error if you use it a with a preceding "concatenate".

Cheers,

--José

[Hakan_Ronningberg · Accepted solution]

Hi José,

It should be possible to use JOIN statements where the same field name occurs in different tables. Unfortunately there seems to be a bug in the SQL connector code. Below is an example how such a JOIN can be written:

SELECT VBRK~NETWR VBRP~NETWR FROM VBRK

JOIN VBRP ON VBRK~VBELN = VBRP~VBELN;

This JOIN should return two fields named NETWR and NETWR01 to Qlik. It is then possible to rename the fields in a LOAD statement.

The Qlik SAP connectivity team is now working to fix the bug. I will write a message here when there is a patch available via Qlik Support.

Regards,

Hakan

[diagonjope]

Hi Hakan,

Thanks for confirming that this issue is due to a bug.

I am looking forward to the patch fixing it.

Cheers,

--José

[Hakan_Ronningberg]

Hi José,

Now there is a patch available at Qlik Support! Refer to the JIRA case SAP-1725 and to this thread.

Regards,

Hakan

[mzavatta]

Hi, where can I download this patch?

We have the same problem!

[diagonjope]

Hi Manuel,

I believe that you have to.contact Qlik Support and ask for the patch.

--José