Checking for previous occurrences within 30 days

[Anonymous]

Hi all,

This is my challenge (simplified):

I have an ID field and a Date field.

For each row I need to check if there is at least 1 occurrence with the same ID within 30 days and flag this with a '1' in a new field called 'Repetition'.

ID    DATE              REPETITION

A     2017-15-08    

A     2017-16-08     1

A     2017-20-08     1

A     2017-30-09

A     2017-19-10     1

How can I achieve this?

Thanks in advance!

[sunny_talwar · Accepted solution]

In the script? Try this

Table:

LOAD * INLINE [

    ID,    DATE

    A,    2017-15-08   

    A,    2017-16-08

    A,    2017-20-08

    A,    2017-30-09

    A,    2017-19-10

];

FinalTable:

LOAD *,

If(ID = Previous(ID) and DATE - Previous(DATE) < 30, 1) as REPETITION

Resident Table

Order By ID, DATE;

DROP Table Table;

[Anonymous]

Fantastic! Simple and smart solution - thank you!