SAP Connector filtering data

[#1]

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.

[Anonymous]

What about trying subselect?

https://help.qlik.com/en-US/connectors/Subsystems/SAP_SQL_Connector_help/Content/6.2/QV-connection/S...

should be something like:

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

[#3]

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.

[Peter_Cammaert]

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.