Remove all but the most current records

[G Gabriel]

Re: Remove all but the most current records

Peter:
You solution will delete records that meet certain dates, subsequently, it could delete all records associated with certain Sec_sub_para if they fall within that range, or conversely, not all of the them and leave more than one if they are outside that range.

It is not a set, but it's more like one.

[Rich Hartnett]

Re: Remove all but the most current records

Gabe I ran the summarize operation and based it on the "Date_Begin" field, and it seems to be working fine. It narrows the table down from approx. 7,000 records to 1949, which is exactly the target number I was expecting it to be.

Unfortunately it creates a new table to do this, which is not exactly what I wanted, but that's okay, I can work with that. It's still a "quick and easy" method of filtering out the unecessary data.

BTW, that's the first time I've used the "Summarize" function. It works like a champ!

[G Gabriel]

Re: Remove all but the most current records

Great.. but you should group by Sec_sub_para no date_begin.
I will try to fix that zip file.

Edited..
Try the zip file now.

Incidentally, if you don't want the extra table, you could write it over the original table, but that's a dangerous practice. You should back up first.

[Rich Hartnett]

Re: Remove all but the most current records

Actually I phrased it wrong. I DID group it by Sec_sub_para , but told it I wanted it summarized by the the LAST "Begin_Date" and it returns the correct records.

[G Gabriel]

Re: Remove all but the most current records

the LAST "Begin_Date

That will work assuming the last date was entered last. To be on thesafe side, I used max instead.

[Rich Hartnett]

Re: Remove all but the most current records

Hmmm Okay, I'll use MAX instead. Thanks.

[Tim Kiebert]

Re: Remove all but the most current records

Hi all,

I have been following along. I tried the summary op as Gabe has it and it works well. I did notice though that the summary op prefixes the field names with the summary type performed. I also tried using the original table as the result table but I end up with a table that has the field names changed but has no records.

I wrote a little xbasic that results in the desired records but in the same table and the same field names.

Dim t as P
dim duptest as C
duptest=""

t=table.open("trafcodetesttable")
t.order("Sec_Sub_Pa+invert(cdate(N5))")
t.fetch_first()
duptest = t.Sec_sub_pa
t.fetch_next()
while .not. t.fetch_eof()
    If t.Sec_sub_pa = duptest then
        t.change_begin()
        t.delete()
        t.change_end()
    else
        duptest = t.Sec_sub_pa
        t.fetch_next()
    end if
end while

Gabe,

I hadn't looked at the form in your example but now I see you had started to make the same script I did. :)

[Tom Cone Jr]

Re: Remove all but the most current records

Tim, I like your script. Suggest a comment on the delete to remind yourself and others that Alpha Five will automatically fetch the next record after doing the delete. I also think it's prudent to close the table when the script ends.

[csda1]

Re: Remove all but the most current records

Rich,

A summarize operation is 1 way to do it, but you can do it even easier using a filter expression that says essentially

' for character Ticknum
datefield==tablemax(tablename,"ticknum='"+ticknum+"'","datefield")

' for numeric Ticknum
datefield==tablemax(tablename,"ticknum="+ltrim(str(ticknum)),"datefield")

If you create the correct ticknum index, it will use LQO and be pretty fast.

Other choices include using DBMAX() instead of TableMax().
You could also use LOOKUPx("C".....) on an index with ticknum+invert(cdate(datefield)) to run even faster

[Tim Kiebert]

Re: Remove all but the most current records

Tim, I like your script. Suggest a comment on the delete to remind yourself and others that Alpha Five will automatically fetch the next record after doing the delete. I also think it's prudent to close the table when the script ends.

Hi Tom,

I agree about the t.close() and normally do, just forgot this time. :o

Also a good point about highlighting the fact that the t.delete() automatically sets the record pointer to the next record. Its to late to edit my post so I've done it here.

[G Gabriel]

Re: Remove all but the most current records

Gabe,

I hadn't looked at the form in your example but now I see you had started to make the same script I did. :)

What a coincidence! it's exactly the same.

[Tim Kiebert]

Re: Remove all but the most current records

What a coincidence! it's exactly the same.

Great minds think a like .... forget about the rest.
Well, I'll say it is similar conceptually but I don't think I can be accused of copy and paste. ;) Take note of the emphasis in my last post.

[G Gabriel]

Re: Remove all but the most current records

No accusation.. I must have been hanging around you long enough, your ideas are starting to rub on me..

[Rich Hartnett]

Re: Remove all but the most current records

"and the hits just keep on comin'" ......................

Hey Tim that little xbasic script you wrote works like a champ. I like it because as you said, I don't have to create a separate table with all those funny field names.

Once again, Mucho Gracias.

[Tim Kiebert]

Re: Remove all but the most current records

No problemo,

Just a word of caution.

Using the sample you provided and the summary operation or my script the 299 records are reduced to 72. If I apply Ira's filter to the 299 records I get 76 as a result. Examing these 76 I see that a couple of the sec_sub_pa values, (21-1205() and 21-1209()), each have three records with the same date. They have however different N7 values. So not sure if there is a problem with the original data but figured I'd bring it to your attention.