1 Reply Latest reply: May 15, 2014 10:14 AM by Olly Bond RSS

    Filtering for current existing value and prior null values

    Janet Grimsley

      I am trying to filter a list for a value eisting on the current day and all prior days having a null value.  My filter works, but needs automation as I do not want to have to edit the filter each day.  Each day there will be a new day's worth of data.  The table below is the table view. The summary view is exported to an mdb file and brought back into Monarch and the filter applied. 

       

      Date     Card Number     Flag

      8/1/2012     789     1

      8/1/2012     321     1

      8/1/2012     891     1

      8/2/2012     567     1

      8/3/2012     145     4

      8/3/2012     789     3

      8/3/2012     321     3

      8/3/2012     112     2

      8/3/2012     123     1

      8/4/2012     891     1

      8/4/2012     456     1

      8/5/2012     891     1

      8/5/2012     101     1

      8/5/2012     132     1

      /CODE

       

       

      Summary is exported to mdb file and brought into Monarch again.  Sorry, the summary view does not want to display correctly, but you get the idea.

       

       

      CardNumber     8/1/2012     8/2/2012     8/3/2012     8/4/2012     8/5/2012

      123               1          

      456                    1     

      789     1          3          

      101                         1

      112               2          

      132                         1

      145               4          

      321     1          3          

      567          1               

      891     1               1     1

      /CODE

       

       

      The current filter I have is:[8/5/2012]>0  .And. IsNull() .And. IsNull().And. IsNull().And. IsNull()

       

      I need a more advanced filtering method or language in order to fully automate this process.

        • Filtering for current existing value and prior null values
          Olly Bond

          Hello rediron

           

          If you export the.summary with the date as an across key with a total column, the the expression to filter the inbound data would be just (value for today) >0 .and. (total) < 2.

           

          For the automation, I'd export it as fixed width text and read it in as a multi column region - as this approach would let me replace the variable key value from the header of the first measure column with a hardcoded field name like (value for today).

           

          Hope this helps

           

          Olly