topic Re: SAP Connector filtering data in QlikView

SAP Connector filtering data

Mon, 26 Jan 2026 21:26:37 GMT

Hi.

We are using the SAP Connector to obtain data from the table GLPCA with a select. But we need to filter the select by PRCTR, otherwise it takes far too long. The problem is, it would be a WHERE PRCTR IN over 5000 values. We cant filter by a list of 5000 values, right?

We solve this later by using the extractor to obtain the hierarchy "hier_0profit_ctr_0106_hier". Here, we can use the field "nodename3" to filter the exact same way as above, by only comparing it to 2 values. This way the data is correct, but by doing it in the QV tables, we are still loading so many records we dont need from SAP and it takes too long.

Is there any way to include the hierarchy in the select so that we can use its fields for the where clause? Or any other solution you may see?

Thanks in advance.

Re: SAP Connector filtering data

Anonymous — Thu, 06 Apr 2017 23:59:23 GMT

What about trying subselect?

https://help.qlik.com/en-US/connectors/Subsystems/SAP_SQL_Connector_help/Content/6.2/QV-connection/SAP-SQL-connection-Su…

should be something like:

SQL SUBSELECT PRCTR FROM GLPCA WHERE PRCTR IN ( SELECT PRCTR FROM <<tableName>> WHERE PRCTR BETWEEN '0000100000' AND '0000300000' );

Re: SAP Connector filtering data

Fri, 07 Apr 2017 06:36:30 GMT

Thanks, that wont work. What you call <<tablename>> is a hierarchy obtained from the extractor, we don't have it in a SAP table. Also, the PRTCR are not secuential. But if we would have that hierarchy in a table we could do

SQL SUBSELECT PRCTR FROM GLPCA WHERE PRCTR IN ( SELECT PRCTR FROM <<tableName>> WHERE nodename3 = 'value1' or nodename3='value2';

That second table is the Organiational Structure, obtained with the SAP extractor. We have it on a QV table. But I don't think we can do a "where in" using a QV table when the values are over 5000.

Re: SAP Connector filtering data

Peter_Cammaert — Fri, 07 Apr 2017 09:20:30 GMT

Last sentence: indeed this may seem like a conflict of "geographically separated tables", but I think it can be done using $-sign substitution.

You should check it out, albeit with a limited set of values, say the first 1000 profit center codes. You can do that with a FOR loop that puts the first 1000 values in a comma-separated list and stores the list in a variable. Let's call the variable vProfitCenters.

Then simply use an OpenSQL statement like

SELECT GL_SIRID RCLNT FROM GLPCA WHERE PRCTR IN ($(vProfitCenters));

and give it a go. Problems you may face:

Length limit of a SQL statement (8k for Oracle?)
Awful performance when using long lists in WHERE clauses due to individual comparisons

Another trick to attempt may be the FOR ALL ENTRIES IN clause, but I'm not sure whether the Connector will accept that variant.