Gustavo Soares Posted June 27, 2019 Share Posted June 27, 2019 Hello there, I need to create a calculated column to indicate if a record was repeated in within an interval of 30 days, but considering some rules Rules to flag as a repetition: 1. Same CPF; 2. DATE difference needs to be less than 30 days; 3. [LOCATION1, LOCATION2] pair needs to be equal beetwen records. 4. PROTOCOL needs to be different; Example: (ID, CPF, PROTOCOL, LOCATION1, LOCATION2, DATE) (1 , '000', 123, 'A', 'AA', '2019-05-01'), (2 , '000', 123, 'A', 'AA', '2019-05-02'), -- This one shouln't be flagged, becauseit doesn'tsatisfythe different protocols clause (3 , '000', 124, 'B', 'AA', '2019-05-20') --This one shouln't be flagged, becauseit doesn'tsatisfythe Locations clause (4 , '000', 121, 'A', 'AA', '2019-05-20'), -- This one needs to be flagged as a repetition, because it happened in less than 30 days, has the same [LOCATION1, LOCATION2], and different PROTOCOL when compared to record ID=1 (5 , '001', 121, 'A', 'AA', '2019-05-20'), -- Not flagged, different CPF (6 , '001', 120, 'A', 'AA', '2019-05-25'), -- Flagged Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now