Identify INC status considering max date

[sriku]

Hi All,

I need a help in identify the incident status considering the max date for each incident and solve the below queries:

Question 1: For each incident I need to flag as "Untouched" and " touched", if max date of each Inc is "Assigned"

Question 2: Once identified as "Untouched" I need to idenified immediate status of Assigned for each Inc (Max endate of each incident) and flag as 1( "Immediate status" and 0 ("Not Immmediate")

Question 3: Need to indenify how many cases are "resolved" immediately before "Assigned" considering max date of start date

Question 4:  Once identified as "resolved" need to calculate how many times that particular incident is falled under resolved

Attached is the data for reference.

Thanks in advance

Regards,

Srikant

[DavidP · Accepted solution]

You can use the summarize tool to find the max (end) date for each incident and join it back to the dataset. From here you can work out the logic for the other fields.

This will get you started. Feel free to post again when you run get stuck.

[sriku]

Hi David,

Thanks for your response.

I can able to find the max date using join and idenfity. But when I flag to know the immediate status of "Assign" my logic is not working properly and it picked other rows.

Regards,

Srikant

[DavidP]

Have a look at the updated workflow attached. It captures the key statuses at Max_end date and joins them back to the main dataset. 

Does this help you to find the immediate status. I'm not 100% sure what the logic for immediate status should be.

[sriku]

For  immediate status, out of all untouched( assigned) all those incident whose max endate and it should be immediate of assigned status

If you see in the data, column flag I have marked with example.

Hope this make understanding.

Regards,

Srikant

[sriku]

1. For identify  untouched and touched the logic should be maximum start date and filter value = "Assigned"

2. Once untouched is identified need to pick the immediate row of untouched, need to make sure the end date is maximum.

Hope this logic makes understable.

Regards,

Srikant

[DavidP]

I still don't really understand the logic you want, but I think it's getting closer. Have a look at the workflow below.

The Summarize Tool finds the Max_Start and Max_End dates for each unique Inc number. Using 2 join tools it then finds the [Value] for each unique [Inc] both where [Start] = [Max_Start] and where [End] = [Max_End].

[Max_Start], [Max_Start Value], [Max_End] and [Max_End Value] are then joined to the original data set.

However, [Max_Start] = [Max_End] except for 4 records where [Max_End] is NULL.

Touched/Untouched is then calculated as follows:

IF [Value]='Assigned' and [Start]=[Max_Start] THEN 'Untouched' ELSE 'Touched' ENDIF

This matches Touched/Untouched from your Excel Sheet in all but 10 Records - True output of the bottom filter tool.

I'm trying to figure out the rule for your second condition by looking at the values you populated for [Flag].

Here is what I'm seeing where [Flag] = 0:

1. [End] is null

2. For [RecordID] 69 that you have as Untouched, [Start] does not equal [Max_Start]

3. For all other cases [Start] = [Max_Start]

Does this look correct? If so, what do you want the rule to be for Condition 2.

What is the rule/pattern for [Flag] = 1? 

[sriku]

Hi David,

Let understand step by step:

1. To identify touched or untouched , filter the date end date = Blank and value = "Assigned", we will get 77 incidents, Flag it as untouched and remaining touched.

2. I wanted to out of 77 untouched incidents, I wanted to know what was it immediate previous status of assigned status for this 77 cases.

If you see for each incident max start date and end date are same.

Hope this logic will make you understand. once this is resolved we will move to remaining problems.

Please consider sheet name "data" to avoid confusion. sheet 1 is for explanation.

Regards,

Sriku

[DavidP]

Here you go

[sriku]

Hi David,

You have almost come to solution.

But when we are picking the previous values, in some incidents are giving wrong status.

Example for below it should pick as "Awaiting user" but as per your solution it is "Assigned"

237	INC242968	1/16/2020 21:31	1/17/2020 11:46	Awaiting 3rd Party
234	INC242968	1/17/2020 11:46		Assigned

Hope it makes clear.