4 Replies Latest reply: May 15, 2014 9:54 AM by Brett_F _ RSS

    Dynamic filtering and exporting

    philcl _

      Short time Monarch user, first time forum user...

       

      Using Monarch Pro 8.02, is it possible to dynamically filter (and export) based on the table column data? 

       

      I need to export data to individually named files based on the column data. Currently I have setup individual hard-coded table filters for each costcentre code, which works fine.  However I have to manually review the numerous filters (say 100) against the source data each time I run the project to ensure I capture any costcentre changes. 

       

      Sample (very rough) logic:

      create filter_set = filter1,

      remove filters from filter_set = filter1,

      variable = table column "costcentre",

      create filter = variable,

      export to variable.txt,

       

      (eg: costcentre code 2345 exports to filename 2345.txt, costcentre code 2455 to filename 2455.txt).

       

      This could be done in two stages; Monarch could create a filter_set (of individual filters) based on the data.  It would have to first remove all filters from the filter_set of the same name.  You could then edit the properties of the filter_set for path and file types etc that would apply to all the individual filters in that filter_set.  The second stage would be as per current; just do a "run-all" from the export table filter menu.  Simple.

       

      My Monarch table is over 110,000 rows, or else I would export the whole table to Excel and write a macro to do this dissection.  Any assistance or pointers from an experienced Monarch user would be greatly appreciated.

       

      I would have thought dynamic filtering would be a standard tool option, but apparently not so.  Monarch if you are listening, please put this feature in version 10 and I will upgrade.

       

      Regards, Phil

        • Dynamic filtering and exporting
          RalphB _

          Hi Phil and welcome to the forum.

           

          Yes you can do something on that order that you want in Monarch.

           

          First off is all your cost centers in one column?  If so, is it possible to do what you want with a runtime parameter.

           

          I would export out a list of unique cost centers in your report via the summary table to whatever you want, database, spreadsheet or text.

           

          I would set up a runtime parameter calculated field with the same format as your cost center column.

           

          Next I would set up a filter where the cost center field is equal to your runtime parameter.

           

          Then I would set up a script to do all the work and to read each of your cost centers one at a time from your table you just created and apply it to your runtime parameter.  Then apply your filter and export it out.

           

          All this can be done via scripts.  Check out the  [url="http://www.datawatch.com/pdf/products/monarch/Monarch_8_Programmers_Guide.pdf"]Programmers Guide[/url] for VB help in scripting or the Help files in Monarch for command line help.

           

          HTH.

           

          Ralph

          • Dynamic filtering and exporting
            philcl _

            Hi there Ralph,

             

            Thanks very much for your advice (I thought it must be possible).  As per your note, I have created the runtime parameter calculated field and a filter that equals the runtime parameter but then get stuck (ie: no black belt).  Can you spell it out in more detail please how I run these steps. 

             

            Also I don't use VB regularly and would prefer to do the whole exercise in Monarch.  I think you are saying that the only way to automate this task is to use some scripting outside of Monarch (ie: to string the steps together)?

             

            Let me know if you need my specific files to look over.

             

            Thanks again, Phil

            philcl@panasonic.com.au[/email]

            • Dynamic filtering and exporting
              RalphB _

              Phil,

               

              I sent you a private message outlining some of the steps.

               

              I hope I didn't make it too confusing or sound too difficult.

               

              Ralph

              • Dynamic filtering and exporting
                Brett_F _

                Originally posted by RalphB:

                Phil,

                 

                I sent you a private message outlining some of the steps.

                 

                I hope I didn't make it too confusing or sound too difficult.

                 

                Ralph /b[/quote]Since this is exactly the same problem I'm trying to solve, I was wondering whether you could post the solution you sent privately here for all of us other "noobs" to review?

                 

                Thanks,

                Brett.