Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.