SAP BEx Connector ignores where clause

[ChristianRoosen]

Hi all,

I am loading data from SAP BW using the SAP BEx connector and an existing query. It works fine as long as there is not too much data in the database. If I want to slice the data (by year) to upload it in smaller pieces and add a where clause to the loading script, it ignores the where clause and the result is the total.

My load script (where the last three rows are the important ones):

LOAD
[A_COMPANY] as [Company],
[A_CUST] as [Customer],
[A_CUST__A_EIC] as [E or IC],
[A_MASTSEG] as [Master Segment],
[A_PRODUCT] as [Product with Packaging],
[A_VTYPE] as [Value Type],
[0CALQUART1] as [Quarter],
[0FISCYEAR] as [Fiscal Year],
[0UNIT] as [Unit of measure],
[00O2TFCI7EI1BJCYHVRTKZIZO] as [Volume],
[00O2TFCI7EI1BJCYHVRTKZVMS] as [Volume KG],
[00O2TFCI7EI1BJCYHVRTL089W] as [Volume LBS],
[00O2TFCI7EI1BJCYHVRTL0KX0] as [Gross Sale LC],
[A_COMPANY__TEXT] as [Company TEXT],
[A_CUST__TEXT] as [Customer TEXT],
[A_CUST__A_EIC__TEXT] as [E or IC TEXT],
[A_MASTSEG__TEXT] as [Master Segment TEXT],
[A_PRODUCT__TEXT] as [Product with Packaging TEXT],
[A_VTYPE__TEXT] as [Value Type TEXT],
[0CALQUART1__TEXT] as [Quarter TEXT],
[0FISCYEAR__TEXT] as [Fiscal Year TEXT],
[0UNIT__TEXT] as [Unit of measure TEXT];

SELECT [A_MP_CPPA_Q0002]
DIMENSIONS (
[A_COMPANY], //Company
[A_CUST], //Customer
[A_CUST__A_EIC], //E or IC
[A_MASTSEG], //Master Segment
[A_PRODUCT], //Product with Packaging
[A_VTYPE], //Value Type
[0CALQUART1], //Quarter
[0FISCYEAR], //Fiscal Year
[0UNIT], //Unit of measure
)
MEASURES (
[00O2TFCI7EI1BJCYHVRTKZIZO], //Volume
[00O2TFCI7EI1BJCYHVRTKZVMS], //Volume KG
[00O2TFCI7EI1BJCYHVRTL089W], //Volume LBS
[00O2TFCI7EI1BJCYHVRTL0KX0], //Gross Sale LC
)
UNITS (
[CURRENCY001.00O2TFCI7EI1BJCYHVRTKZIZO], //CURRENCY001 Volume
[UNIT001.00O2TFCI7EI1BJCYHVRTKZIZO], //UNIT001 Volume
[CURRENCY002.00O2TFCI7EI1BJCYHVRTKZVMS], //CURRENCY002 Volume KG
[UNIT002.00O2TFCI7EI1BJCYHVRTKZVMS], //UNIT002 Volume KG
[CURRENCY003.00O2TFCI7EI1BJCYHVRTL089W], //CURRENCY003 Volume LBS
[UNIT003.00O2TFCI7EI1BJCYHVRTL089W], //UNIT003 Volume LBS
[CURRENCY004.00O2TFCI7EI1BJCYHVRTL0KX0], //CURRENCY004 Gross Sale LC
[UNIT004.00O2TFCI7EI1BJCYHVRTL0KX0], //UNIT004 Gross Sale LC
)
VARIABLES (
[NAME=E_V_E_CURRENCY_0001, SIGN=I, OPTION=EQ, LOW=EUR], //Currency (Single value, mandatory)
[NAME=A_COMPANY_AUTH, SIGN=I, OPTION=EQ, LOW=330], //Company (auth. relevant)
[NAME=A_EIC_MUL_OPT, SIGN=I, OPTION=EQ, LOW=E], //E or IC
)

FROM [A_MP_CPPA]
WHERE [0FISCYEAR] = '2016';
// WHERE A_CUST = '100029';

I have tried with [], with eq and =, with '2016' and "2016", with () around the whole clause, with year and with customer (and both exist!) all with the same result that I get all customers for all years.

I found a similar question from 2015 and the solution was to upgrade to the connector version 6.x, I am on V7.0.3.

Any ideas?

Thanks
Christian

[Hakan_Ronningberg]

Hi,

The BEx Connector does not support using WHERE clause. Restricting data is done by using variables defined in the BEx query.

Regards,
Håkan

[ChristianRoosen]

Tack så mycket Håkan for the quick reply!

I did all this only because I get an error message when resticting the data with the query's variables. The message is:

The following error occurred:

Exception condition "NO_PROCESSING" triggered

Three of the variable work fine, the fourth variable for the year throws the error message.

VARIABLES (
[NAME=E_V_E_CURRENCY_0001, SIGN=I, OPTION=EQ, LOW=EUR], //Currency (Single value, mandatory)
[NAME=A_COMPANY_AUTH, SIGN=I, OPTION=EQ, LOW=321], //Company (auth. relevant)
[NAME=A_EIC_MUL_OPT, SIGN=I, OPTION=EQ, LOW=E], //E or IC
[NAME=E_FYEAR, SIGN=I, OPTION=EQ, LOW=K42016], //Current Fiscal Year
)

Any hint on that?

Thanks

Christian

[Hakan_Ronningberg]

Hi Christian,

Not sure what the problem is. You can test the query in SAP. If it works in transaction RSRT it should work in the BEx connector as well.

Regards,
Håkan

[ChristianRoosen]

Hi Håkan,

it works in SAP (for years already) and it does not work in the BEx connector throwing the error "Exception condition "NO_PROCESSING" triggered" . If I change the query and delete the variable "year" then it works in the BEx connector until I run into a memory error if too much data is fetched at a time. 

Best regards,
Christian

[Hakan_Ronningberg]

Hi again,

OK. I suggest you contact Qlik Support and refer to this thread!

Regards,
Håkan