Required repeated complaint device no

[MS90]

I want device no  for a  devices coming for same repeat complaint comments (complaint- column).

Both tables join on Repair code-column ,some times complain records shows  same comments which  was for same date but timestamp varies in minutes and seconds.
my required output is need to show deviceno for the same complain which the deviceno came  for old date please find attached data for reference 

[LRuCelver]

I don't quite understand what it is you want.

Do you want to discard repeat complaints per device and day, in other words only keep the oldest complaint of a type for every device and day?

Or do you want a table with the repeating comlaints (the "newer" ones) or the repeated complaints (the "oldest" ones)?

I assume you need to do this in the script and not the frontend, correct?

[Dataintellinalytics]

As per snapshot when you join these two tables you should get the expected result which is Device NO for each Complaint.

Please share snapshot of your output and what is expected.

[MS90]

I need the repeated complaints oldest ones,in script I tried through previous function it is showing all records if the complaint is repeated for latest date also

[MS90]

 i need highlighted o/p only but by using previous function it is showing current date records also a repeated complaint 

[LRuCelver]

Ta make talking about our data a bit easier, I've added a new column ROWNO:

ROWNO	REPAIR CODE	TIME STAMP	COMPLAINT
1	A1	13/3/2024 1:00:21 PM	GENERAL SERVICE
2	A1	13/3/2024 1:01:21 PM	GENERAL SERVICE
3	A2	13/2/2024 4:10:00 PM	GENERAL SERVICE
4	A3	12/2/2024 11:01:21 AM	BOARD CHANGE
5	A3	12/2/2024 11:02:21 AM	BOARD CHANGE
6	A4	1/1/2024 2:02:35 AM	BOARD CHANGE
7	A5	13/3/2024 1:01:21 AM	MERCURY ADDED
8	A5	13/3/2024 1:02:21 AM	MERCURY ADDED
9	A6	12/2/2024 11:00:21 AM	MERCURY ADDED
10	A7	13/3/2024 1:01:21 PM	GENERAL SERVICE
11	A8	12/2/2024 11:02:21 AM	BOARD CHANGE

From what I understand from your post and reply to my initial comment, you want the oldest repeating comment, with repeating comments being those with more than one of the same COMPLAINT per REPAIR CODE per day. This would result in these ROWNOs: 2, 5 and 8

In your reply to @Dataintellinalytics you said you wanted ROWNOs 3, 6 and 9. These rows are the oldest entries for their respective values in the COMPLAINT field.

I'm thus assuming you need the oldest entry for every COMPAINT value, regardless of the REPAIR CODE.

You can use FirstSortedValue to select only the oldest value when grouping the data in the load script:

Data:
NoConcatenate Load
	FirstSortedValue([REPAIR CODE], [TIME STAMP]) as [REPAIR CODE],
    Timestamp(Min([TIME STAMP])) as [TIME STAMP],
    COMPLAINT
Group By COMPLAINT;
Load
	[REPAIR CODE],
    Timestamp(Timestamp#([TIME STAMP], 'D/M/YYYY hh:mm:ss TT')) as [TIME STAMP],
    COMPLAINT
Inline [
REPAIR CODE, TIME STAMP, COMPLAINT
A1, 13/3/2024 1:00:21 PM, GENERAL SERVICE
A1, 13/3/2024 1:01:21 PM, GENERAL SERVICE
A2, 13/2/2024 4:10:00 PM, GENERAL SERVICE
A3, 12/2/2024 11:01:21 AM, BOARD CHANGE
A3, 12/2/2024 11:02:21 AM, BOARD CHANGE
A4, 1/1/2024 2:02:35 AM, BOARD CHANGE
A5, 13/3/2024 1:01:21 AM, MERCURY ADDED
A5, 13/3/2024 1:02:21 AM, MERCURY ADDED
A6, 12/2/2024 11:00:21 AM, MERCURY ADDED
A7, 13/3/2024 1:01:21 PM, GENERAL SERVICE
A8, 12/2/2024 11:02:21 AM, BOARD CHANGE
];

[Dataintellinalytics]

So you want only the minimum timestamp for each Complaint ?

[MS90]

It should display only all the repeat complaint for previous timestamp 

[Dataintellinalytics]

Was the solution given by LRuCelver not helpful ?

[MS90]

No I am not able to get output,after creating the above script and also   getting data island