2 Replies Latest reply on May 7, 2016 12:39 AM by james_frew

    Testing For IS Not + OR - and then Deleting

    james_frew

      Before reinventing the wheel, let me explain what I want to achieve.

       

      I have a very large number of records containing incremental measurements. These are in a set order.  I want to smooth that data and isolate those records whose data falls 10 measurements lower or 10  higher than the previous record.

      If a record's data IS NOT plus of minus 10 that record is omitted and deleted. The next record will be evaluated against the previous successful test.

      This will result in data incrementing by +/-10.

       

      The aim is reduce the number of records [file size and memory] by retaining only those records rising or falling by 10. 

       

      I would appreciate the communities advice on the best way forward.

       

      Thank you in advance.

        • 1. Re: Testing For IS Not + OR - and then Deleting
          erolst

          Here's a basic looping script that identifies the records to delete, then isolates them; at a guess, this is faster then deleting records one by one.

           

          Go to Record [ first ]

          Set Variable [ $prevMeas ; Table::measurement ]

          Loop

             Go to Record [ next ]

             Set Variable [ $curMeas ; Table::measurement ]

             If [ not ( $curMeas = $prevMeas + 10 or $curMeas = $prevMeas - 10 ) ]

               Set Variable [ $deletionList ; List ( $deletionList ; Table::primaryKey ) ]

             Else

               Set Variable [ $prevMeas ; $curMeas ]

             End If

             Exit Loop if [ Get ( RecordNumber ) = Get ( FoundCount ) ]

          End Loop

          If [ not IsEmpty ( $deletionList ) ]

            # [ assuming a utility relationship by Table::gIDList = Table_self_byIDList::primaryKey ]

            Set Field [ Table::gIDList ; $deletionList ]

            Go to Related Record [ from: Table_self_byIDList; Layout: Table ; matching only ]

            Delete Found Records [ no dialog ]

          End If

          1 of 1 people found this helpful
          • 2. Re: Testing For IS Not + OR - and then Deleting
            james_frew

            Thanks you so much for that.

            However, I could not set up the 'utility relationship' with the global field, as you suggest. Nor could I get the

            If [ not ( $curMeas = $prevMeas + 10 or $curMeas = $prevMeas - 10 ) ]

                 Set Variable [ $deletionList ; List ( $deletionList ; Table::primaryKey ) ]

               Else

                 Set Variable [ $prevMeas ; $curMeas ]

               End If

            to work.

            I did modify the If [ not ( $curMeas = $prevMeas + 10 or $curMeas = $prevMeas - 10 ) ] step to run with the ≤ and ≥ instead of the = operator.

            I also simply set the script to delete each unwanted record as it looped. Processing 200,000+ records worked quicker than I thought.

             

            I also added a chart step so that I could view the data graphically. Lovely.

             

            I would have loved to have set up the utility relationship table as you suggest just to see which runs quickest and less demanding on processors.  I was not clear on that part [sorry].

             

            That said, you saved me much trial and error.

             

            Thank you so so much for your kind help.