This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
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?
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?
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.