How to find / highlight / delete the duplicate rows - by comparison of time columns

[Pavan Kumar Sayani]

Hello Connections,

 

I have a problem in Filtering condition to delete the duplicate rows in a table.

Example table : Columns ==> A, B, C, D, E ( start time in DATETIME ) , F ( End time in DATE TIMEformat), G, H and so on......

Present expression applied to delete the duplicate rows:Rank ( baserowid(),"asc", [A], , [D], [E], [F] ) = 1 (Thanks to tibco community) ======> Itgives the result correctly butnot matched my requirement [90% of data is ok ] due to above expression not meet the requirment.

Present Problem : Remaining duplicate rows to be delete by validation of Start & End time comparison when Column [A], , [D] are equal in duplicate rows.

 

ROW 1 : Column [A] = xxx , = yyy , [D] = zzz, [E] value : 23-07-202115:25:20 and [F] value : 23-07-2021 15:28:40 --> Required row data in final table

ROW2 :Column [A] = xxx , = yyy , [D] = zzz,[E] value : 23-07-2021 15:26:10 and [F] value : 23-07-2021 15:28:40 --> Not required becausestart &end time is within the limit of ROW 1 --> So, Row2 not required in final table.

ROW3:Column [A] = xxx , = yyy , [D] = zzz,[E] value : 23-07-2021 15:25:20 and [F] value : 23-07-2021 15:27:29--> Not required becausestart& end time is within the limit of ROW 1 --> So, Row3is not required in final table.

 

 

FYI, Source data is coming through Information links but i can not modify the Information link area.

 

It would be great & appreciable, if you could help on solving the above issue.

 

 

Thanks for your sincere response.