Show data within 2 days of each other 1

[ptrifile]

I have a simple report that displays date,type and origin. Is it possibly to show only those records that are duplicated by origin and type within X amount of days of each other? Any help or suggestions are appreciated.

Thank you!

Paul

 

[CrystalizeCanada]

If you have a table called Table with say 4 fields

Id
Date
Type
Origin

you could add the table twice to the Database Expert.
The Table will show twice the second time it will be called Table2

Link Id to the Id on both tables and choose the Not Equal on the Linking Options under link type. (this stops the record linking to itself)
Link Type to Type.
Link Origin to Origin.
Don't link Date.
All Joins would be Left Outer Joins.

You will get every record match every other record (other than itself) as long as the Origin and Type are the same.
Now we need to create a filter to filter out the records where the date the first report in not within two days of the date in the second table.

{Table.Date} in {Table2.Date} -1 to {Table2.Date} + 1

This may give you the results you are looking for

(If you don't have an id field then you can still get results but it can be trickier)


Gordon BOCP
Crystalize

 

[ptrifile]

Thank you, that did exactly what i was looking for. I just have one more question. If i want to filter by one more field (disposition) where only those that were withing the X amount of days had the same disposition how would i go about that? Thanks again for any help!

Pual

 

[CrystalizeCanada]

Add that condition to the filter and see if that works
{Table.Date} in {Table2.Date} -1 to {Table2.Date} + 1 and {Table.disposition} = {Table2.disposition}



Gordon BOCP
Crystalize