issue occurances within a column

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?

Expression

Accepted answers

mmenth

Ahhh good catch. I forgot that the 'DateAdd' function is not in the standard Alteryx package.

Try using this formula for the multi-row tool:

IF [Op Id]=[Row-1:Op Id] AND [Run Date] = DateTimeFormat(DateTimeAdd([Row-1:Run Date],1,'day'),'%Y-%m-%d') THEN [Row-1:error] ELSE [Row-1:error]+1 ENDIF

Best,

mmenth

All comments

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

count errors.yxmd

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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335