Finding repetitive entries for every ID (multiple occurrence) within a specific day range

[breaksrmine]

Hey guys,

I am struggling to solve a problem, where I have to find repetitive entries for an ID, but not only starting from the first occurrence, but for every within a specific day range.

for example:

My table contains 3 columns (ID, startdate, code) sorted by ID, startdate

I need to know wether a code repeats itself within 30 days for every ID.

line 3 is a repetition of line 2 (same ID and same code within 30 days)

line 5 is a repetition of line 3 ...

line 8 is a repetition of line 7...

line 12 is a repetition of line 11...

line 13 is a repetition of line 12...

line	ID	startdate	code	repetition
1	B10004	19.01.2024	1401
2	B10004	14.03.2024	1601
3	B10004	10.04.2024	1601	X
4	B10004	01.05.2024	701
5	B10004	03.05.2024	1601	X
6	B10005	07.02.2024	1101
7	B10005	21.02.2024	1606
8	B10005	11.03.2024	1606	X
9	B10006	01.02.2024	1101
10	B10006	11.07.2024	1003
11	B10006	23.07.2024	1001
12	B10006	25.07.2024	1001	X
13	B10006	19.08.2024	1001	X
14	B10006	01.10.2024	701

Thank you in advance for your help.

regards

Alex

[jpenuliar]

[tempA]:

Load *, Autonumber(ID&'|'&code) as key

inline [

line , ID , startdate , code

1 , B10004 , 19/01/2024 , 1401  

2 , B10004 , 14/03/2024 , 1601  

3 , B10004 , 10/04/2024 , 1601

4 , B10004 , 01/05/2024 , 701  

5 , B10004 , 03/05/2024 , 1601

6 , B10005 , 07/02/2024 , 1101  

7 , B10005 , 21/02/2024 , 1606 

8 , B10005 , 11/03/2024 , 1606

9 , B10006 , 01/02/2024 , 1101  

10 , B10006 , 11/07/2024 , 1003  

11 , B10006 , 23/07/2024 , 1001  

12 , B10006 , 25/07/2024 , 1001

13 , B10006 , 19/08/2024 , 1001

14 , B10006 , 01/10/2024 , 701  

];

NoConcatenate

[tempB]:

Load *,

If(key=Peek(key)  

, Interval(startdate - Peek(startdate) , 'D') ) as "Interval Days",

If(key=Peek(key) 

, If(Interval(startdate - Peek(startdate),'D') <=30 , 'X' , '')) as "Repetition" 

Resident [tempA]

Order by line asc;

Drop Table [tempA];

[breaksrmine]

@jpenuliar Thanks for your help! This only seems to work when entries are one below the other

[jpenuliar · Accepted solution]

I would change the the line

Resident [tempA]

Order by line asc;

to

Resident [tempA]

Order by key asc;

[breaksrmine]

@jpenuliar This worked, thank you very much. You could edit your first post so I mark your first reply as solution then.