3 Replies Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    A report that tells me what's NOT there!

    diabolik _

      I'm having a pretty good time extracting data from Monarch, and have been for a while. I tell my friends it's like doing puzzles all day.

       

      However, we've been having weird issues with our vendor that provides our data, where stuff that's supposed to be there is missing. I would like to write a report in Monarch that returns what's not there[/I], so I can call our vendor and raise heck.

       

      For example:

       

      STUFF THAT IS SUPPOSED TO BE IN THE DATA FILES[/B]

      Apples

      Bananas

      Cherries

      Donuts

      Eggplants

      Figs

      Grapes

       

      STUFF THAT ACTUALLY CAME DOWN FOR THE DAY[/B]

      Bananas

      Donuts

      Figs

      Grapes

       

      DESIRED MONARCH REPORT OUTPUT[/B]

      Apples

      Cherries

      Eggplants

       

      Is this feasible?

        • A report that tells me what's NOT there!
          Data Kruncher

          A belated "Welcome to the forum!" Diabolik.

           

          If you're using Monarch Pro, then this can be fairly straightforward. If that's not the case, well, then there's some work ahead.

           

          I'll assume that you've got Pro.

           

          First, open a Monarch project that loads your "[B]STUFF THAT IS SUPPOSED TO BE IN THE DATA FILES[/B]". Maybe that's a report, perhaps it's an Excel list or even a .csv file.

           

          Now in the Table window create an external lookup which references the "[B]STUFF THAT ACTUALLY CAME DOWN FOR THE DAY[/B]" file, using the common field, say "Fruit". Import the Fruit field from the external file as FruitCheck.

           

          Monarch will give you

          Fruit       FruitCheck

          Apples      Apples[/code]when Apples exists in both files, and

          Fruit       FruitCheck

          Apples     (Null)[/code]when Apples does not exist in the external file (the daily file).

           

          That said, the appearance of the value of (Null) is controlled in the Options->View menu so it might be blank, (Null), or some other value of your choice.

           

          Now you can build a filter to isolate the items that don't exist in the daily file by using this expression:

          IsNull(FruitCheck)[/code]or for reference for those without the IsNull function:

          Fruit<>FruitCheck[/code]Save your model.

           

          PS. Do donuts qualify as fruit? Maybe apple fritters?

           

          HTH,

          Kruncher