Automate

Hi guys

I'm looking to develop a query script which looks at both the Product Hierarchy fields in the Basic Data 1 (MARA-PRDHA) and Sales Org 2 (MVKE-PRODH)  material master views to ensure they match BUT, want the query to ONLY retrieve any potential mismatches. Does anyone know of a way to do this rather than download both fields for all materials then filter it manually in excel?

Hi guys

I'm looking to develop a query script which looks at both the Product Hierarchy fields in the Basic Data 1 (MARA-PRDHA) and Sales Org 2 (MVKE-PRODH)  material master views to ensure they match BUT, want the query to ONLY retrieve any potential mismatches. Does anyone know of a way to do this rather than download both fields for all materials then filter it manually in excel?

------------------------------
Andrew Lewis
Global Master Data Analyst
QuidelOrtho
Wales
United Kingdom
www.QuidelOrtho.com
------------------------------

Hi Andrew

As Jayasri mentions, we don't have the ability to compare fields in Studio Query. 

Here are a few thoughts:

(1) Create a query that matches those two tables on Material Number and Hierarchy, with a left outer join.

Then use search criteria to say that Product Hierarchy in MARA is not blank (you may not need this but I do in my demo system) , and then say Sales org is null - which means there wasn't a match on Material and Product hierarchy, i.e. they don't match

You'd get results something like this:

You can always download the list of suspect materials and then use a 2nd query to pull in what is in Basic & Sales using an IN operator, pointing the the list of materials.

(2) you can compare fields in SQL via SQVI or SQ01 or a tcode like that.  If you grant permissions to that SAP Query to the Studio users you create in SAP, you can find it and run it from Studio via the Infosets option.  

Feel free to open a request in the Ideas portal for this feature - it would be great to have.

Best Regards,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.

Original Message:
Sent: 04-28-2025 04:00
From: Andrew Lewis
Subject: Query to retrieve any non matching data from two tables

Hi guys

I'm looking to develop a query script which looks at both the Product Hierarchy fields in the Basic Data 1 (MARA-PRDHA) and Sales Org 2 (MVKE-PRODH)  material master views to ensure they match BUT, want the query to ONLY retrieve any potential mismatches. Does anyone know of a way to do this rather than download both fields for all materials then filter it manually in excel?

------------------------------
Andrew Lewis
Global Master Data Analyst
QuidelOrtho
Wales
United Kingdom
www.QuidelOrtho.com
------------------------------

Thanks Sigrid

What I'm ideally looking to create is like what you have suggested by I need to see ALL of the Product Hierarchy's in both MARA and MVKE whether they're blank or populated and also if the Sales Views haven't been created then the value should be blank so that it leads to further investigations as to why. Could you suggest to me how I can do that? I'll keep playing around until I can find a solution that works.

Thanks

Hi Andrew

As Jayasri mentions, we don't have the ability to compare fields in Studio Query. 

Here are a few thoughts:

(1) Create a query that matches those two tables on Material Number and Hierarchy, with a left outer join.

Then use search criteria to say that Product Hierarchy in MARA is not blank (you may not need this but I do in my demo system) , and then say Sales org is null - which means there wasn't a match on Material and Product hierarchy, i.e. they don't match

You'd get results something like this:

You can always download the list of suspect materials and then use a 2nd query to pull in what is in Basic & Sales using an IN operator, pointing the the list of materials.

(2) you can compare fields in SQL via SQVI or SQ01 or a tcode like that.  If you grant permissions to that SAP Query to the Studio users you create in SAP, you can find it and run it from Studio via the Infosets option.  

Feel free to open a request in the Ideas portal for this feature - it would be great to have.

Best Regards,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.

Original Message:
Sent: 04-28-2025 04:00
From: Andrew Lewis
Subject: Query to retrieve any non matching data from two tables

Hi guys

I'm looking to develop a query script which looks at both the Product Hierarchy fields in the Basic Data 1 (MARA-PRDHA) and Sales Org 2 (MVKE-PRODH)  material master views to ensure they match BUT, want the query to ONLY retrieve any potential mismatches. Does anyone know of a way to do this rather than download both fields for all materials then filter it manually in excel?

------------------------------
Andrew Lewis
Global Master Data Analyst
QuidelOrtho
Wales
United Kingdom
www.QuidelOrtho.com
------------------------------

Hi Andrew

The same query I suggested with a left outer join will work for your purposes.  Just remove the conditions on sales org is null and product hierarchy is not null.

The key is the left outer join on MVKE - so it will include all materials, and if sales data exists, it will be pulled in.  If no sales data exists, it will still be included.

Hope that helps,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.

Original Message:
Sent: 06-19-2025 06:56
From: Andrew Lewis
Subject: Query to retrieve any non matching data from two tables

Thanks Sigrid

What I'm ideally looking to create is like what you have suggested by I need to see ALL of the Product Hierarchy's in both MARA and MVKE whether they're blank or populated and also if the Sales Views haven't been created then the value should be blank so that it leads to further investigations as to why. Could you suggest to me how I can do that? I'll keep playing around until I can find a solution that works.

Thanks

------------------------------
Andrew Lewis
Global Master Data Analyst
QuidelOrtho
Wales
United Kingdom
www.QuidelOrtho.com

Original Message:
Sent: 05-01-2025 13:48
From: Sigrid Kok
Subject: Query to retrieve any non matching data from two tables

Hi Andrew

As Jayasri mentions, we don't have the ability to compare fields in Studio Query. 

Here are a few thoughts:

(1) Create a query that matches those two tables on Material Number and Hierarchy, with a left outer join.

Then use search criteria to say that Product Hierarchy in MARA is not blank (you may not need this but I do in my demo system) , and then say Sales org is null - which means there wasn't a match on Material and Product hierarchy, i.e. they don't match

You'd get results something like this:

You can always download the list of suspect materials and then use a 2nd query to pull in what is in Basic & Sales using an IN operator, pointing the the list of materials.

(2) you can compare fields in SQL via SQVI or SQ01 or a tcode like that.  If you grant permissions to that SAP Query to the Studio users you create in SAP, you can find it and run it from Studio via the Infosets option.  

Feel free to open a request in the Ideas portal for this feature - it would be great to have.

Best Regards,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.

Original Message:
Sent: 04-28-2025 04:00
From: Andrew Lewis
Subject: Query to retrieve any non matching data from two tables

Hi guys

I'm looking to develop a query script which looks at both the Product Hierarchy fields in the Basic Data 1 (MARA-PRDHA) and Sales Org 2 (MVKE-PRODH)  material master views to ensure they match BUT, want the query to ONLY retrieve any potential mismatches. Does anyone know of a way to do this rather than download both fields for all materials then filter it manually in excel?

------------------------------
Andrew Lewis
Global Master Data Analyst
QuidelOrtho
Wales
United Kingdom
www.QuidelOrtho.com
------------------------------