Automate

 View Only

  • 1.  How to pull all data including empty fields in a query script.

    Posted 06-19-2025 04:35

    Guys

    Does anyone know how to get a query script to pull not only data but empty fields as well to see what fields are missing data?

    For example, I created a query to pull the product hierarchy data but it is only returning fields that have been populated in both MARA and MVKE, it doesn't return any fields that are missing and blank and I need to capture these as well?



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


  • 2.  RE: How to pull all data including empty fields in a query script.

    Posted 06-23-2025 12:21

    Hi Andrew

    Does the query have criteria or is it running wide open? Are you able to share the query so we can take a look at how it's set up? Or did you resolve this with your other post about a query for hierarchy mismatch?



    ------------------------------
    Vanessa Kutasi
    Foundation Admin./Application Analyst II
    Idexx Operations, Inc.
    USA
    ------------------------------

    Original Message


  • 3.  RE: How to pull all data including empty fields in a query script.

    Posted 06-25-2025 08:28
      |   view attached

    Hi Vanessa

    I created a query script that seemed to work fine until I pulled a report from MARA to show a different number of materials than the unique number of materials in the query.

    What I'm trying to achieve is the query pulls ALL of the materials from MARA, populates the product hierarchy (even if it's blank) then, does the same for MVKE, pulling the product hierarchy for all the different sales orgs (whether blank or if the sales org hasn't been created). That way we can investigate any mismatches and any blanks.

    I've attached the query script so you can see what it's pulling. Sigrid kindly suggested the Left outer join and that pulled a lot more data but not everything?

    Please let me know of any suggestions you may have.

    Thanks

    Andy



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

    Attachment(s)

    Qsq
    S4 - Product Hierarchy Query.Qsq   369 KB 1 version
    Original Message


  • 4.  RE: How to pull all data including empty fields in a query script.

    Posted 06-26-2025 18:10

    Hi Andrew

    I was able to obtain the results you are looking for, however was only successful when using 2 queries chained together. When pulling as 1 query, data was missing or inconsistent. 

    I used a handful of materials which I knew met your criteria of missing or populated hierarchies and both scenarios populated in the output. 

    I added a default criteria for material description language of EN for table MAKT.SPRAS. Adding this criteria prevents duplication of the output. 

    The only criteria I used to get the results were material #s. I left sales org. and material type blank. I find with Query, that the less you give it to look for, the better the results. Each criteria is an "And", so if a material doesn't meet every piece of criteria, it will be negated from the output. 

    I added a sheet to do the compare. So, the first query runs (MARA), then the 2nd query runs (MVKE), the MVKE data pulls into the compare sheet, at which point there is a lookup to the MARA sheet to pull those hierarchies. Then, finally, there is a match #N/A means there is NO match, 1 means there is a match. 

    I've attached the queries and template. It's a bit extra work up front, but in order to get accurate results, this was the only way I found to work. 

    Hopefully it helps in your quest!



    ------------------------------
    Vanessa Kutasi
    Foundation Admin./Application Analyst II
    Idexx Operations, Inc.
    USA
    ------------------------------

    Original Message