Remove the duplicate id row which has two different status and show in a table one record based on max date.

[MEDHA07]

Hi All,

I am having the data like one ID with multiple status.I will consider the status as two categories.

One is Review and second is Completed.Under review ,open ,Review and change will come.

completed is consider the Close,Positive,Positive/clear.

So, Now One pivot table i want to get the Only Completed id count. Since i had the duplicate ids with same status with different users i am able to get distinct record based max date.This one i tried with max date with resident load.

My second Scenario I have same id which has two status Positive and Positive/clear like as shown in  below table.

Those are consider the completed category but i want to get only one record should present in straight table.

In pivot i am getting distinct count. But when user want to see the details in straight table there it is displaying all the records which are available.Since Completed catageory has two status with one id with two different dates,Based on max date it should show only latest records for completed .

But as per below table i want to get out put like mentioned second table.

Statsu:Open,change,close,review,Positive,Positive/clear

ID	STATUS	UpdatedDate	User
23657	Open	2021-11-05 13:01:12	R1234
23657	Review	2021-11-05 13:02:53	SH748
23657	Positive	2021-11-11 10:24:29	SH748
23657	Positive/Clear	2021-11-11 10:26:29	H1234

OutPut expected:

ID	STATUS	UpdatedDate	User
23657	Open	2021-11-05 13:01:12	R1234
23657	Review	2021-11-05 13:02:53	SH748
23657	Positive/Clear	2021-11-11 10:26:29	H1234

Thanks,

HM.

[MEDHA07 · Accepted solution]

if( ID =Previous(ID) and match("STATUS,'Positive') =previous(match( STATUS,'Positive/Clear')),1,2) as FlagM,

if i select output value is 2 it it is working.tested with my dataset for all records.