12 Replies Latest reply: May 15, 2014 9:54 AM by Nick Osdale-Popa RSS

    Hey all you filter Experts

    PokeFan88 _

      I'm wondering if you can help me with something?  I have a scenario with a sales/invoices report and am questioning filtering possibilities.  To fill you in I have a header, footer, & detail template PLUS a few append templates.  All with multiple fields.

       

      Within these invoices I am trying to exclude certain invoices that meet a requirement.  So far all I've been able to do is exclude that line item that meets the particular requirement.  The following is the filter I have in place:

       

      Instr("offset",ITEM_NO)=0 /This allows only orders that do NOT have the word offset included in  the item#/

       

      The filter does a great job of pulling that particular "detail" out, but I am wanting to pull the entire invoice data from the report.

       

      Hope this make sense.  Any insight and/or help you can provide would be great.

       

      Thanks,

      Shae

        • Hey all you filter Experts
          Mike Urbonas

          I think I understand.  A certain invoice may have several detail lines, but if the word "offset" appears in the item number in one or more of the invoice lines, you don't want to display ANY of the detail lines for that invoice.  Correct?

           

          Monarch Pro's ability to combine report-based data with data from another data source (Excel file, Access file, ODBC database, etc.) will be very helpful here.  Upgrading from Standard to Pro is well worth every penny.

           

          First, using Monarch Pro, create a simple model that captures just the invoice number and the item number.  Then create a calculated field like this (call it: Offset in Item Number?)

           

          IF(Instr("offset",ITEM_NO)=0,"No",Yes")

           

          (If the word offset does not appear within ITEM_NO, then the calc.field will display No, otherwise the calc. field will display the word Yes.)

           

          Now go to the Summary view and create a summary with the invoice number and the calc. field Offset in Item Number? as Key Fields.  Very important: specify to Monarch that you want the field Offset in Item Number? to display Across.  Finally, select "count" as the Summary field. 

           

          Your Summary should look something like this:

          [font="courier"]INVOICE NUMBER                NO                 YES      SUM(count)

          1001                        2                      1         3

          1002                        2                      0         2

          1003                        1                      2         3

          (etc.)[/font][/quote]Export this summary to Excel, Access, etc...your choice.  What we have just done is created an outside table we can now use in Monarch Pro to lookup whether a particular invoice has any invoice lines with "offset" in the item number.

           

          Save and close the model, and now open the model you have already created.  In Monarch Pro, go to the Table view and click File -> Join Database -> Create Join.  You want to join to the Excel/Access/other file you just created.  The Join columns you will select are the invoice number in the Excel/Access/other file you just created, with the invoice number column in your Table view.  Finally, when prompted to select the columns to import, you will select the YES field.

           

          The YES field should now appear in your table, something like this:

           

          [font="courier"]INVOICE NUMBER               ITEM#                  AMOUNT      YES

          1001                        offset                    7.00    1    

          1001                        abc                       5.00    1

          1001                        XYZ                      13.25    1

          1002                        abc                       8.00    0

          1002                        abc                       2.50    0

          1003                        offset                    9.00    2

          1003                        offset                    8.00    2

          1003                        abc                       5.00    2

          (etc.)[/font][/quote]Now the filter is easy:

           

          YES=0

           

          and ALL lines from each invoice that has at least one invoice line with an item number containing "offset" are filtered out.

           

          Did this post solve your need?

           

          Mike Urbonas

          Product Marketing Manager

          Datawatch Corporation

           

          [size="1"][ February 26, 2003, 02:59 PM: Message edited by: Mike Urbonas ][/size]

          • Hey all you filter Experts
            PokeFan88 _

            I'm still working on it.  I think it will work, but my problem now is that my "YES=0" are getting exported as "Null".

             

            Shae

            • Hey all you filter Experts
              PokeFan88 _

              I fixed the "null" problem in Excel with a small calculation.  Is there a way around this within Monarch?

               

              By the way, it works great!  Thanks.

              • Hey all you filter Experts
                Winn _

                Mike.

                 

                I hate to steal your thunder about Monarch Pro, but here is a procedure that will work for all of us v6 Standard users.

                 

                1. Set up your model anyway that you want. It doesn't matter how you have it set up. This procedure just adds two calculated fields to your table.

                 

                2. Create a calculated field (let's name it "OK or Invoice Number") formatted the same way the INVOICE NUMBER field. (I am assuning that the invoice number is character type.) Use the following calculation:

                 

                IF(INSTR("offset",ITEM_NO)=0,"OK",INVOICE NUMBER)

                 

                This wil create a column that will either show that the item is OK, or will return the invoice number of that item if the word "offset" is found.

                 

                Now the complicated part.

                 

                3. Create a calculated field as a lookup style. (Let's call it "Valid Invoice".) Format it as a character type. Click on the Lookup tab. Set the Default Value to "Yes".

                 

                4. In the Input field box, select OK or Invoice Number. Click on the Get Input Field Values button.

                 

                This will fill your lookup table with the invoice numbers of the "offset" items.

                 

                5. Change the Input field box to INVOICE NUMBER. Click the OK button.

                 

                What this does is make Monarch lookup the invoice number from a list of invoices that are to be excluded. If Monarch finds a match in the list, then it returns a blank to the Valid Invoice field. If it does not find a match then it returns "Yes" to the Valid Invoice field. (I know that this is somewhat backwards logic, but hey it works.)

                 

                6. Set up a filter.

                 

                Valid Invoice="Yes"

                 

                You are done!

                 

                However, every time that you run this model, you will need to edit the Valid Invoice field, by deleting all the values in the table and repeating steps 4 and 5. I believe that this is easier than creating two models and an external file.

                 

                Let me know if you have any problems with this procedure.

                • Hey all you filter Experts
                  Mike Urbonas

                  Winn,

                   

                  Your use of a Lookup calculated field is ingenious!  I concur that your solution is superior, because (1) it works in either version of V6 and (2) is a complete solution to Shae's problem within a single Monarch session.  Well done!

                   

                  Mike

                  • Hey all you filter Experts
                    Grant Perkins

                    Here's an idea that may or may not work for your situation but has a chance given that invoices and related reports tend to be quite structured.

                     

                    This works, for example, on the Classic.prn tutorial report. However if your report is simply a line listing or database dump it could be trickier - sounds like it isn't from your description.

                     

                    Make one of your append templates for the INVOICE NUMBER level cover enough lines to include all the report data from the first line of the append to the last possible detail line before the next occurrence of the append. Typically that would be your next invoice number. Hopefully you are unlikely to have more lines than Monarch allows before forcing a break. (If you have you have some invoice!)

                     

                    For the first detail line in the selection define a data field that you can be sure will pick up some data. What it contains and its size may not be important. But it must include some data somewhere.

                     

                    Set the Advanced Parameters for the field to Start On and then use your particular criteria - "offset" being the target in this case. (if it the last data on the detail line we may have a problem!)

                     

                    I assume leaving the 'End Field On' set to Line Count = 1 will probably be ok for your report.

                     

                    What you should get is a new field that has some data in it ONLY if one of the detail lines contains the word "offset". But all detail lines for the invoice number should be populated. Therefore you can filter directly on the field by selecting only records where the field is EMPTY.

                     

                    Clearly there are limitations, but if the report will allow the technique without discrepancies for any of the data lines you have a one shot option.

                     

                    I can email the relevant model for the classic.prn format if it would. Send me a private message with an email address.

                     

                    Hope the above makes sense when you read it and I hope I haven't missed an obvious problem. Can't see one here except the caveat about won't work in all circumstances probably.

                     

                    Grant

                     

                    Originally posted by PokeFan88:

                    I'm wondering if you can help me with something?  I have a scenario with a sales/invoices report and am questioning filtering possibilities.  To fill you in I have a header, footer, & detail template PLUS a few append templates.  All with multiple fields.

                     

                    Within these invoices I am trying to exclude certain invoices that meet a requirement.  So far all I've been able to do is exclude that line item that meets the particular requirement.  The following is the filter I have in place:

                     

                    Instr("offset",ITEM_NO)=0 /This allows only orders that do NOT have the word offset included in  the item#/

                     

                    The filter does a great job of pulling that particular "detail" out, but I am wanting to pull the entire invoice data from the report.

                     

                    Hope this make sense.  Any insight and/or help you can provide would be great.

                     

                    Thanks,

                    Shae /b[/quote]

                    • Hey all you filter Experts
                      Mike Urbonas

                      Hi Grant, I think your suggestion would also work as long as the invoices are all one page, or the invoice number is not repeated on page two of the invoice.  In such a case the big Append field you are suggesting would end upon finding the "next" invoice number, except in this case would be the same[/i] invoice number, repeated on page 2.  Bottom line, YMMV depending on report layout.

                      • Hey all you filter Experts
                        Grant Perkins

                        Hi Mike,

                         

                        I totally agree with you, hence my circumspection in the description! However its worth considering if for no other reason than it forces the developer to look at the 'report' structure in detail. It may also work in a number of cases but I would hesitate to apply the idea without a careful check of the resulting table.

                         

                        My probable approach if it failed would be the one you outlined, especially if there was a chance of re-using the invoice number list for, say, a period of time and applying it to different analyses in that period (e.g. Invoices we raised last month).

                         

                        On the other hand Winn's solution looks very interesting as well and I plan to have a play with it to see how it may be applied and file the ideas in the mental cache for future consideration.   smile.gif[/img] 

                         

                        What does YMMV stand for? Perhaps me being half asleep but I can't figure that one today.

                         

                        Grant

                         

                         

                        Originally posted by Mike Urbonas:

                        Hi Grant, I think your suggestion would also work as long as the invoices are all one page, or the invoice number is not repeated on page two of the invoice.  In such a case the big Append field you are suggesting would end upon finding the "next" invoice number, except in this case would be the same[/i] invoice number, repeated on page 2.  Bottom line, YMMV depending on report layout. /b[/quote]

                        • Hey all you filter Experts
                          Mike Urbonas

                          Originally posted by Grant Perkins:

                          What does YMMV stand for? Perhaps me being half asleep but I can't figure that one today.

                           

                          Grant

                           

                           

                          /quoteYMMV = Your Mileage May Vary

                          • Hey all you filter Experts
                            Grant Perkins

                            Thanks Mike. New one to me but I like it and it's completely correct in this case. The one step process has its limits but is worth a look to see if it suffices. If not, pick a 2 step route from those excellent solutions offered.

                             

                            Grant

                             

                            Originally posted by Mike Urbonas:

                              /size[quote]quote:[/size]Originally posted by Grant Perkins:

                            What does YMMV stand for? Perhaps me being half asleep but I can't figure that one today.

                             

                            Grant

                             

                             

                            /quoteYMMV = Your Mileage May Vary /b[/size][/QUOTE]

                            • Hey all you filter Experts
                              PokeFan88 _

                              Originally posted by Grant Perkins:

                              Thanks Mike. New one to me but I like it and it's completely correct in this case. The one step process has its limits but is worth a look to see if it suffices. If not, pick a 2 step route from those excellent solutions offered.

                               

                              Grant

                               

                                /size[quote]quote:[/size]Originally posted by Mike Urbonas:

                                 /size[quote]quote:[/size]Originally posted by Grant Perkins:

                              What does YMMV stand for? Perhaps me being half asleep but I can't figure that one today.

                               

                              Grant

                               

                               

                              /quoteYMMV = Your Mileage May Vary /b[/size][/QUOTE][/b][/size][/QUOTE]Guys I have read all of the suggestions and have implemented a couple of them.  Thank-you very much.  I will probably have other questions now that I know what a good resource this is.

                               

                              Thanks,

                              Shae

                              • Hey all you filter Experts
                                Nick Osdale-Popa

                                Guys I have read all of the suggestions and have implemented a couple of them. Thank-you very much. I will probably have other questions now that I know what a good resource this is.

                                 

                                Thanks,

                                Shae /quoteYMMV

                                 

                                Only kidding, we're here to help!