2 Replies Latest reply on May 17, 2012 2:00 AM by djordje.vukovic

    Advanced filter

      I have table with columns id, timestamp and several other columns. I want to select start and end date (I created parameters for those dates) get all id's that has timestamp within selected date range. Then, I want to filter out all rows containg different id (id that was not in selected range). Can you suggest me easiest way to achive this?


      Thanks,

      Djordje

        • 1. Re: Advanced filter
          Tracy Rodgers

          Hi Djordje,

           

          A calculated field will need to be created using the parameters similar to the following:

           

          if [timestamp]>=[Parameter 1] and [timestamp]<=[Parameter 2] then 'show' else 'hide' end

           

          Then, place this calculation on the filters shelf. Check 'show' and click OK. Then, right click on each parameter and select Show Parameter Control.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Advanced filter

            Thank you for you answer Tracy. The code snippet you wrote will show only rows that has timestamp in selected range. What I want to achieve is to keep all rows with IDs such there is at least one transaction with that ID in selected timestamp range. My primary idea to achieve this is to somehow get set of all IDs from rows that has timestamp in selected range. And then for every row to check if its ID is in selected set and if not to exclude that row.

            This is example what I want if I have such table:

            ID, timestamp

            1, 1000

            2, 2000

            3, 3000

            2, 5000

            2, 10000

            If I select timestamp >= 1000 and timestamp <= 2000 I want to get this result

            1, 1000

            2, 2000

            2, 5000

            2, 10000