5 Replies Latest reply: May 15, 2014 9:58 AM by Data Kruncher RSS

    Filter building

    blazzercat _

      I am building a very large filter that looks like this:

       

      <{2006-09-30} .And. PAYTO="6704aa" .Or. PAYTO="1337aa" .Or. PAYTO="2620aa" etc........

       

      The problem is there are over 400 "PAYTO" entries. I don't want add one at a time. I have a list of the "PAYTO" codes in an Excel worksheet in column 2. Does anyone know of a means to build this filter using a script or something?

       

      Thanks

        • Filter building
          Data Kruncher

          You know, I'm sure that there's a better way to do this, but... (how's that for an inspiring start       )

           

          Copy this code to your Excel file. Right click the sheet name and select View Code, then paste it in.

           

          [font="courier"]Sub BuildFilter()

              Dim rng As Range

              Dim sFilter As String

               

              sFilter = "[Trans Date]<{2006-09-30} .And. PAYTO .In. ("

              For Each rng In Range("B1", Range("B1").End(xlDown))

                  sFilter = sFilter & Chr(34) & rng.Value & Chr(34) & ", "

              Next rng

              sFilter = Left(sFilter, Len(sFilter) - 2) & ")"

              Range("D1").Value = sFilter

          End Sub  /font[/quote]The expression for your Monarch filter will be in cell D1. If you've already got something there, change the D1 reference in the last line of code to reflect a cell you know is blank when you get started.

           

          Now just run the macro, and copy the cell to your Monarch filter window.

           

          That's one way to leverage Excel with Monarch.

          • Filter building
            blazzercat _

            What a genius! Thanks...saves a lot of work.

             

            I did have one more thing. How do I change the expression to exclude these PAYTO codes?

            • Filter building
              Data Kruncher

              Thanks blazzercat!

               

              To exclude the codes, just use the .NotIn. operator instead of the .In. operator.

               

              That'll do it.

               

              To make it easy, and obvious to you later on (I've tripped up on my own stuff once or twice         ), you could rename the macro to "BuildIncludeFilter" and duplicate it as "BuildExcludeFilter", changing the .In. to .NotIn. as necessary.

               

              Kruncher

               

              [size="1"][ October 03, 2007, 10:22 PM: Message edited by: Data Kruncher ][/size]

              • Filter building
                Nigel Winton

                Kruncher

                You are correct, there is another way.

                First in the Excel spreadsheet insert a new column on the right of the PAYTO column and put a 1 in this new column against all entries in the PAYTO column.

                In Monarch, use External Lookup to link to the spreadsheet and link the PAYTO columns to bring in the new column with the 1 in.

                This should put a 1 against all those in both the spreadsheet and Monarch and leave the others blank.

                Then build your filter in Monarch to have the date required and 1 in the new column.

                 

                <{23/05/07}.and.'New Column'= 1

                 

                This way you do not have to rebuild the filter in Excel each time you run the report and by changing the filter to pick up <>1 you can have both filters in Monarch.

                You can of course use anything in the 'New Column' in the Excel Spreadhseet if you do not like 1, as long as you filter on it. Also if you needed any extra filtering you could use different numbers for different purposes.

                You can hide the 'New Column' if you do not need it in your output.

                 

                Kruncher, I do like your use of VBA to help Monarch, really honestly.

                 

                 

                Regards

                 

                Nigel

                • Filter building
                  Data Kruncher

                  It occurs to me that another solution is possible.

                   

                  You could build an external lookup creating a MATCHED field, and return the PAYTO value when found. Those not matched would be null. Then the MATCHED field could be used in the filter, checking for null or not null as appropriate for the desired include or excluded condition. You could build an Include filter that checked for MATCHED not null (the equivalent of .In.), and and Exclude filter that picked up null values (the equivalent of .NotIn.).

                   

                  No code nor filter editing required.

                   

                  One other thing you might consider, blazzercat, is adding a runtime parameter field to your model for the date you need for the filter. Then you wouldn't need to edit the filter to change the date when you use that model from time to time.

                   

                  Kruncher