issue occurances within a column

[slckkrky]

Issue List

Hi all

I am receiving a daily file which shows data failures  for a field and appending information to a master file. When there is no error at a given date, a record on OP ID  99999 is created.

I would like to count how many times an issue happened and how long it took to resolve it. I tried summary tool but I can only get first and latest date the issue happened. However trick is that I would like to count the issue per operation id for each individual occurrence. For example for Op Id 542145, 2 different times error happened and it took 3 day to fix in total (2 days for first error, 1 day for 2nd error), so result should be 2 error 3 days. For 545287 op id, there is 1 error resolved in 2 days.

 Can you advice me how to tackle this?

[Paulteryx]

Greetings,

I see that you have 3 entries in your table for 542145, my question is how do you determine that you have 2 errors? Similarly, how do you know there is only one error for 545287 when there are 2 entries in the table. I think your question is missing a little context to help make this easier to interpret.

Cheers,

Paulteryx

[mmenth]

Hi @slckkrky,

I am assuming you knew because it showed errors on two consecutive days, is that right? If that's the case, I would fist sort your date on op ID with run date oldest to newest. Then I would add a multi-row formula tool where you are creating a new field that picks out the error number. From there you can group by op ID and error number and take a count distinct of the dates. 

See attached for an example.

Best,

mmenth

[slckkrky]

Hi Paul

Imagine this main files is fed by daily files that are capturing daily errors. So if an op is repeated consecutively it means same error is still there and not fixed. There for it is a one error.  Does it makes sense?

[slckkrky]

Hello Mmenth

I checked your solution. It looks fine but issue for 542145 continued between 02/04/2019 and 03/04/2019 therefore this should be counted as 1 error. And the error on 05/04/2019 is the 2nd error.

[slckkrky]

Mmenth

In fact this is  the type of solution I am looking for however imagine in one day 5 new errors appaerad, another day 2 another day 7. This way your code

"IF [Op Id]=[Row-1:Op Id] AND [Run Date] =  DateTimeAdd([Row-1:Run Date],1,'day') THEN [Row-1:error] ELSE [Row-1:error]+1 ENDIF" wouldnot work I think. Because it is considering to check the row one below. Don't you think?

[mmenth]

Hey @slckkrky,

That's why I sorted it first so that it is looking at opIDs individually. The only thing my solution would not capture is if there were two different errors for the same opID on the same day or on consecutive days. So basically, my solution assumes that errors showing up on consecutive days are the same.

What you might also add is Min(Run Date) to the summary tool at the end so that you can see when the error first occurred and Max(Run Date) to see when it last occurred. Maybe that will help demonstrate how my solution works a little more.

Best,

mmenth

[mmenth]

I should say that the 'error' field I created is not the number of errors, but rather the error sequence number. The 'CountDistinct_Run Date' is the number of days that error lasted.

[slckkrky]

Hi Mmenth

Yes I got it. and it is exactly my solution. And there wont be same error within same day. Errors in consecutive days will be same error.

However why do I have different result? Did you change  something?

[mmenth]

I didn't change anything except adding the min and max dates to the summary tool. Here it is again in case something happened to the original one I sent you.

Best,

mmenth