Automate

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.
------------------------------

Great tip, Sigrid.  Yes, I use dynamic lookups extensively and frequently chain queries together based on the results of the previous query.

In terms of use cases, I have around 60 daily scheduled query jobs that identify data defects/anomalies, mostly associated with the Material Master.  I also use an automated non-Studio job to read all of the result files and alert Material Master Data Stewards of anything that should be looked into.  This process is the basis of our Material Master data quality and auditing process.

While we stress getting data right the first time, people do make mistakes.  For us, the automated jobs and alerts give us a means to quickly deal with critical errors that could impact International Trade (e.g. country of origin) or accounting (e.g. valuation class, price unit, profit center, etc.) BEFORE any postings are made against the Material.

I've developed a lot of experience with the query tool and rely on it heavily.  Sometimes, I create hybrid solutions where my queries use dynamic data from HANA CV, refreshes, other Excel files, or literally wherever I can connect to data.

I only wish that my transaction skills were as good as my query skills!!!

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.
------------------------------

That's fabulous to hear, @Pat Shaner, thanks for sharing and well done! I think it's a great use case for Query, and one that I've heard of prior but on a smaller scale.

Is anyone else doing something similar or quite different? This is a great place to share challenges but also ideas on how to get more value from the products you have.

And happy to help with any Transaction skills if you post it on the community. :)

Best Regards,

Sigrid

Great tip, Sigrid.  Yes, I use dynamic lookups extensively and frequently chain queries together based on the results of the previous query.

In terms of use cases, I have around 60 daily scheduled query jobs that identify data defects/anomalies, mostly associated with the Material Master.  I also use an automated non-Studio job to read all of the result files and alert Material Master Data Stewards of anything that should be looked into.  This process is the basis of our Material Master data quality and auditing process.

While we stress getting data right the first time, people do make mistakes.  For us, the automated jobs and alerts give us a means to quickly deal with critical errors that could impact International Trade (e.g. country of origin) or accounting (e.g. valuation class, price unit, profit center, etc.) BEFORE any postings are made against the Material.

I've developed a lot of experience with the query tool and rely on it heavily.  Sometimes, I create hybrid solutions where my queries use dynamic data from HANA CV, refreshes, other Excel files, or literally wherever I can connect to data.

I only wish that my transaction skills were as good as my query skills!!!

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.
------------------------------

Yes thank you Sigrid for your Tip of the week (should say, tipS of the weekS).

I am always struggling with Material Master in the sense that we have Materials which are shared across organizations. I want to be able to query and provide business with, for example, Sales Org Status of a Material that doesn't match with Plant Status. I use MARA, MVKE and MARC. For example from MARA select Material Type and description, criteria on MARC-MMSTA "Plant status is PT" and then show the Sales Status (MVKE-VMSTA) if not ST. Because a Material may exist in multiple Sales Orgs and/or Plants which are not linked to each other, the result is redundant rows showing all for all. 

So, i was thinking about a reference data list or a Excel in which I could put the relationship like;

SalesOrg AB00 with Plant AB00

SalesOrg AB00 with Plant AB01

SalesOrg BB00 with Plant AB00

SalesOrg BB00 with Plant BB00

SalesOrg BB00 with Plant BB01

But still... I wouldn't know how to apply...

Not sure if it has impact, I run these queries on the server and scheduled by jobs (like once per month).

Any suggestions?

That's fabulous to hear, @Pat Shaner, thanks for sharing and well done! I think it's a great use case for Query, and one that I've heard of prior but on a smaller scale.

Is anyone else doing something similar or quite different? This is a great place to share challenges but also ideas on how to get more value from the products you have.

And happy to help with any Transaction skills if you post it on the community. :)

Best Regards,

Sigrid

Great tip, Sigrid.  Yes, I use dynamic lookups extensively and frequently chain queries together based on the results of the previous query.

In terms of use cases, I have around 60 daily scheduled query jobs that identify data defects/anomalies, mostly associated with the Material Master.  I also use an automated non-Studio job to read all of the result files and alert Material Master Data Stewards of anything that should be looked into.  This process is the basis of our Material Master data quality and auditing process.

While we stress getting data right the first time, people do make mistakes.  For us, the automated jobs and alerts give us a means to quickly deal with critical errors that could impact International Trade (e.g. country of origin) or accounting (e.g. valuation class, price unit, profit center, etc.) BEFORE any postings are made against the Material.

I've developed a lot of experience with the query tool and rely on it heavily.  Sometimes, I create hybrid solutions where my queries use dynamic data from HANA CV, refreshes, other Excel files, or literally wherever I can connect to data.

I only wish that my transaction skills were as good as my query skills!!!

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.
------------------------------

Hi Ed

Thanks for sharing your use case. I think I have run into this prior, but I can't remember where.

Would table TVKWZ help? I believe it shows the valid combinations between sales org + dist channel to plant:

If this isn't it, please let me know.

Best Regards,

Sigrid

Yes thank you Sigrid for your Tip of the week (should say, tipS of the weekS).

I am always struggling with Material Master in the sense that we have Materials which are shared across organizations. I want to be able to query and provide business with, for example, Sales Org Status of a Material that doesn't match with Plant Status. I use MARA, MVKE and MARC. For example from MARA select Material Type and description, criteria on MARC-MMSTA "Plant status is PT" and then show the Sales Status (MVKE-VMSTA) if not ST. Because a Material may exist in multiple Sales Orgs and/or Plants which are not linked to each other, the result is redundant rows showing all for all. 

So, i was thinking about a reference data list or a Excel in which I could put the relationship like;

SalesOrg AB00 with Plant AB00

SalesOrg AB00 with Plant AB01

SalesOrg BB00 with Plant AB00

SalesOrg BB00 with Plant BB00

SalesOrg BB00 with Plant BB01

But still... I wouldn't know how to apply...

Not sure if it has impact, I run these queries on the server and scheduled by jobs (like once per month).

Any suggestions?

That's fabulous to hear, @Pat Shaner, thanks for sharing and well done! I think it's a great use case for Query, and one that I've heard of prior but on a smaller scale.

Is anyone else doing something similar or quite different? This is a great place to share challenges but also ideas on how to get more value from the products you have.

And happy to help with any Transaction skills if you post it on the community. :)

Best Regards,

Sigrid

Great tip, Sigrid.  Yes, I use dynamic lookups extensively and frequently chain queries together based on the results of the previous query.

In terms of use cases, I have around 60 daily scheduled query jobs that identify data defects/anomalies, mostly associated with the Material Master.  I also use an automated non-Studio job to read all of the result files and alert Material Master Data Stewards of anything that should be looked into.  This process is the basis of our Material Master data quality and auditing process.

While we stress getting data right the first time, people do make mistakes.  For us, the automated jobs and alerts give us a means to quickly deal with critical errors that could impact International Trade (e.g. country of origin) or accounting (e.g. valuation class, price unit, profit center, etc.) BEFORE any postings are made against the Material.

I've developed a lot of experience with the query tool and rely on it heavily.  Sometimes, I create hybrid solutions where my queries use dynamic data from HANA CV, refreshes, other Excel files, or literally wherever I can connect to data.

I only wish that my transaction skills were as good as my query skills!!!

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.
------------------------------

hi Sigrid, yes that would be a good one.

Would you mind to share your thoughts on how to connect the 4 tables (MARA-MARC-MVKE-TVKWZ)? So, my use case (as example) would be to get from a Material Type, all materials in MARC with status (MMSTA) the corresponding (TVKWZ) entry/entries from MVKE with field value VMSTA.

(would bring in MAKT for the description)

Placing the tables in Query, the connections are automatically made.

Hi Ed

Thanks for sharing your use case. I think I have run into this prior, but I can't remember where.

Would table TVKWZ help? I believe it shows the valid combinations between sales org + dist channel to plant:

If this isn't it, please let me know.

Best Regards,

Sigrid

Yes thank you Sigrid for your Tip of the week (should say, tipS of the weekS).

I am always struggling with Material Master in the sense that we have Materials which are shared across organizations. I want to be able to query and provide business with, for example, Sales Org Status of a Material that doesn't match with Plant Status. I use MARA, MVKE and MARC. For example from MARA select Material Type and description, criteria on MARC-MMSTA "Plant status is PT" and then show the Sales Status (MVKE-VMSTA) if not ST. Because a Material may exist in multiple Sales Orgs and/or Plants which are not linked to each other, the result is redundant rows showing all for all. 

So, i was thinking about a reference data list or a Excel in which I could put the relationship like;

SalesOrg AB00 with Plant AB00

SalesOrg AB00 with Plant AB01

SalesOrg BB00 with Plant AB00

SalesOrg BB00 with Plant BB00

SalesOrg BB00 with Plant BB01

But still... I wouldn't know how to apply...

Not sure if it has impact, I run these queries on the server and scheduled by jobs (like once per month).

Any suggestions?

That's fabulous to hear, @Pat Shaner, thanks for sharing and well done! I think it's a great use case for Query, and one that I've heard of prior but on a smaller scale.

Is anyone else doing something similar or quite different? This is a great place to share challenges but also ideas on how to get more value from the products you have.

And happy to help with any Transaction skills if you post it on the community. :)

Best Regards,

Sigrid

Great tip, Sigrid.  Yes, I use dynamic lookups extensively and frequently chain queries together based on the results of the previous query.

In terms of use cases, I have around 60 daily scheduled query jobs that identify data defects/anomalies, mostly associated with the Material Master.  I also use an automated non-Studio job to read all of the result files and alert Material Master Data Stewards of anything that should be looked into.  This process is the basis of our Material Master data quality and auditing process.

While we stress getting data right the first time, people do make mistakes.  For us, the automated jobs and alerts give us a means to quickly deal with critical errors that could impact International Trade (e.g. country of origin) or accounting (e.g. valuation class, price unit, profit center, etc.) BEFORE any postings are made against the Material.

I've developed a lot of experience with the query tool and rely on it heavily.  Sometimes, I create hybrid solutions where my queries use dynamic data from HANA CV, refreshes, other Excel files, or literally wherever I can connect to data.

I only wish that my transaction skills were as good as my query skills!!!

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.
------------------------------