Tip of the Week – Query In Operator dynamic values from Excel
When extract data from Query, sometimes you want your Query criteria to be dynamic. That can include using a list of values in the Criteria via an IN operator. The IN operator can work from a static list of values or a dynamic one.
Why would you want to use dynamic values?
· You might have a list of values that gets updated from another system. Example – list of materials from a PLM system that need to be extended to plants, etc.
· You might have a few chained queries that need to use the same input criteria for each query. Example – project systems budget planned vs actual is in separate structures/tables, so use the IN operator to read the search criteria from.
· You have a form with a set of values, perhaps Business Partner numbers in a repeating table. You want to use those in the IN operator to read data from SAP and then perhaps extend them in a form.
How does this work? In Query, select a field as criteria
Then go to the Criteria tab
· Select the IN operator
· Click on the …
· Click Dynamic lookup then Configure Dynamic list
· Select the sheet, column and start row and fille them in – you can see the data below – trimming spaces is optional. So below I am stating Project Number will be in Column B, starting in row 3, and there may be many rows
If you only have one value, you can specify an end row – From 3 through 3, for example.
· When you Test/Run you will see the criteria
· If you click the … next to each field on the right, you will see the values it is using from the Excel file dynamically
· For this use case I had a second Query that pulled actuals, and it used the same Query Criteria
· Two queries, chained together, using the same search criteria.
Note that alternatively, you can point to another file and read the files from that instead of embedding it in your workbook.
· Fill in the file path and do the same type of configuration with Column, start and end rows
I've also used the output of one query to feed the list of values as input search criteria for the next query – example: Return the list of Customer business partners in the US, then used a second query to extract company data with the BP number as input using the IN operator.
This gives you a lot of flexibility to dynamically use list of values for your Query criteria for one or multiple Queries, without having to change the search criteria for each use case.
Have you used this technique in other use cases? If so, please share your great work!
Happy Querying!
Sigrid
------------------------------
Sigrid Kok
*Precisely Software Inc.
------------------------------