Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristianRoosen
Contributor
Contributor

SAP BEx Connector ignores where clause

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

5 Replies
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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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