Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Fantastic! Simple and smart solution - thank you!