Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Selecting current duplicate records & Count based on window

HM
8 - Asteroid

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

8 REPLIES 8
s_pichaipillai
12 - Quasar

HM,

i still dont understand the 2nd logic for COunt

can explain little more Please?

s_pichaipillai
12 - Quasar

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
5 - Atom

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
5 - Atom

Correction, Status="ok"

JohnJPS
15 - Aurora

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:

ProjectStatusSummarySnip.JPG

 

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
8 - Asteroid

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:00PMand 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/152/1/153/1/154/1/155/1/15
    Project AOkBadOkOkOk

 

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

 

Thanks

s_pichaipillai
12 - Quasar

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

HMDup.PNG

HMDup1.PNG

HM
8 - Asteroid

Thank you so much! The solution is perfect :)

Labels