topic Re: Finding repetitive entries for every ID (multiple occurrence) within a specific day range in App Development

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

breaksrmine — Wed, 18 Dec 2024 10:08:02 GMT

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

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

jpenuliar — Wed, 18 Dec 2024 12:13:53 GMT

[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];

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

breaksrmine — Thu, 19 Dec 2024 09:30:30 GMT

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

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

jpenuliar — Wed, 18 Dec 2024 16:08:50 GMT

I would change the the line

Resident [tempA]

Order by line asc;

to

Resident [tempA]

Order by key asc;

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

breaksrmine — Thu, 19 Dec 2024 12:39:10 GMT

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