Selecting current duplicate records & Count based on window

[HM]

Hi,

There are 2 objectives I'd like to achieve but I'm not quite sure how to go about it:

1. Out of the Duplicate Records per project in the 'Period' Column, I'd like to include ONLY the most current record based on the 'Created On' column
2. Count the number of consecutively 'Ok'  status records if the most current status is 'Ok' per project

Dataset:

Project	Status	Created On	Period
A	Good	1/1/15 1:00PM	1/1/15
B	Good	1/1/15 2:00PM	1/1/15
A	Ok	2/1/15 1:00PM	2/1/15
A	Ok	5/1/15 3:56PM	5/1/15
B	Ok	2/1/15 2:00PM	2/1/15
A	Ok	3/1/15 4:00PM	3/1/15
A	Bad	2/1/15 5:00PM	2/1/15
A	Ok	4/1/15 2:00PM	4/1/15
A	Good	3/1/15 1:00PM	3/1/15
A	Bad	3/1/15 2:00PM	3/1/15

Desired Output:

Project	Status	Created On	Period	Consecutively Ok
A	Good	1/1/15 1:00PM	1/1/15	3
B	Good	1/1/15 2:00PM	1/1/15	1
A	Ok	5/1/15 3:56PM	5/1/15	3
B	Ok	2/1/15 2:00PM	2/1/15	1
A	Ok	3/1/15 3:00PM	3/1/15	3
A	Bad	2/1/15 5:00PM	2/1/15	3
A	Ok	4/1/15 2:00PM	4/1/15	3

Thanks

[s_pichaipillai]

HM,

i still dont understand the 2nd logic for COunt

can explain little more Please?

[s_pichaipillai]

for the first logic Please refer the attached for removing dups

1. I converted the Created date to date time format

2. Sort by Accending then rank them 

[treasa]

Hi

For Part 1, i just sorted it desending for Created on, the used Unique for Project and Period, finally Sorted again Created on Ascending

for Part 2, I used  a formula tool to create Flags field  ie one field for Project=A and Status= Ok, this takes value 1 if true and 0 otherwise. Similarly i created another flag field for Project=B and Stauts =0.Next I attacjhed a Running total tool grouped by Project on both the flag fileds. Finally i summarised with group by as Project and Max for both flag fields. This will give you the max times 'ok' occurs consecutively for each project. from here you can append it to the main data.

Let me know if this makes sense, else ill attach the workflow as well.

[treasa]

Correction, Status="ok"

[JohnJPS]

I've attached a workflow that generates a "Project Status Summary" based on the input: it lists a project, status, and days in that status.  Browse-tool output looks like:

What's nice about this is that we can tell at a glance how little time was spent in "bad" project status. But otherwise, understood it isn't exactly what you asked for.

[HM]

Thank you all for attempting to assist me with my problem - it's greatly appreciated.

Allow me to describe it in a different format:

1. In Part 1, people can submit status reports for projects and occasionally they may submit one with incorrect data (e.g. the wrong status) so they resubmit another one. These incorrect records can be identified by the same project name and period (e.g. Project A had 3 status reports submitted for the period 3/1/15). I'd like to exclude the 2 incorrect ones (e.g. the record with a created on date of 3/1/15 1:00PM and 3/1/15 2:00PM) and only include the correct record in the dataset which would be the last one to be submitted for the period 3/1/15 (e.g. the record with a created on date of 3/1/15 4:00PM). The same logic would be applied for Project B and other projects that may have duplicate/incorrect records.
2. In Part 2, after the duplicate/incorrect records have been exclude, if the data (slightly modified) is sorted based on Project Name and Period, it would appear like the below. I would like to be able to flag if the project's most current record (i.e. 5/1/15) has the status of Ok and if it does, to count the records starting from the latest record (i.e. 5/1/15) to the record before the status is changed (3/1/15). Although the 1/1/15 record has a status of 'Ok' as well, it should not be counted since the one after that has a status of 'Bad' (i.e. it is not consecutively Ok). Therefore, for project A, the 'Consecutively Ok' field should only have the number 3 which symbolises 3 records/days (i.e. 3/1/15, 4/1/15 and 5/1/15).

   	1/1/15	2/1/15	3/1/15	4/1/15	5/1/15
   Project A	Ok	Bad	Ok	Ok	Ok

I hope that clarifies things a bit more but please feel free to ask more questions if it's still confusing.

Thanks

[s_pichaipillai · Accepted solution]

Hi HM,

i think i understood correctly :-)

i did slight modification to the one already attached

it give the below outputs. let us know if this helps

[HM]

Thank you so much! The solution is perfect :)