Evaluate a series of IDs within a group and flag when any of the IDs Repeats.

[JPSeagull]

I am trying to resolve a challenge given me.

I have a series of Tasks with lines tracking actions on kits that are used which is straightforward. However, when within the same Task ID, we want to know if any of the Kits are repeated. That is, if the kits used are cycled through and then one used earlier in the series is brought back, we need to flag that. What compounds this we have NULLs which I can filter out. 

I tried to use the Multi-row formula, but I get the error that the Kit ID is an unknown variable.

In the end, I am trying to build out a summary view like below either in Alteryx or take the pre-summarized data over to Tableau to do this.

Task ID	Total Entries	Kit Repeated after Break?
1001	5	Yes
1002	4	No
1003	8	No
1004	9	Yes
1005	11	No
1006	4	No
1007	14	Yes

[atcodedog05]

Hi @JPSeagull 

Here is how you can do it.

Workflow:

Hope this helps : )

[JPSeagull]

@ atcodedog05 

Thanks for the quick reply, but this doesn't result as expected. If you look in the Kit Repeated after Break? column in the results, the 5th row with #93 is now repeated and should be 'Yes." Everything above it should N/A. However, this column header would be for a summary. So, what I looking for is only populate something in the column once that Kit number repeats within the same Task ID and that repeating means only if there is a Kit between it.

[atcodedog05]

Hi @JPSeagull 

Is this the expected output if not please provide the expected output.

Hope this helps : )

[JPSeagull]

@atcodedog05

Having a by line list helps the evaluation and this would be the results as I am looking for if a Kit ID repeated within the Task ID set (not over the entire data set).

Task ID	Entry ID	Kit ID	Kit Repeated after Break?
1001	1	93	No
1001	2	93	No
1001	3	241	No
1001	4	241	No
1001	5	93	Yes
1002	1	17	No
1002	2	17	No
1002	3	18	No
1002	4	84	No
1003	1	17	No
1003	2	17	No
1003	4	17	No
1003	5	17	No
1003	6	17	No
1003	7	23	No
1003	8	26	No
1004	1	22	No
1004	2	22	No
1004	3	7	No
1004	4	56	No
1004	5	56	No
1004	7	7	Yes
1004	8	7	Yes
1004	9	7	Yes
1005	1	93	No
1005	2	93	No
1005	3	93	No
1005	4	93	No
1005	5	55	No
1005	6	55	No
1005	7	41	No
1005	8	41	No
1005	9	41	No
1005	10	41	No
1005	11	41	No
1006	1	52	No
1006	2	52	No
1006	3	52	No
1006	4	21	No
1007	1	12	No
1007	2	12	No
1007	3	12	No
1007	4	12	No
1007	5	62	No
1007	6	62	No
1007	7	78	No
1007	8	78	No
1007	10	62	Yes
1007	11	12	Yes
1007	12	79	No
1007	14	12	Yes

And a Summary View would look like this (the blank/Nulls are counted in total line entries):

Task ID	Total Entries	Kit Repeated after Break?
1001	5	Yes
1002	4	No
1003	8	No
1004	9	Yes
1005	11	No
1006	4	No
1007	14	Yes

[atcodedog05]

Hi @JPSeagull 

Please find the updated workflow.

Hope this helps : )

[gabrielvilella]

Why the first two KitID 93 are set to No when you have the same 93 within the same TaskID 1001? Same question for the KitID 241.

Task ID	Entry ID	Kit ID	Kit Repeated after Break?
1001	1	93	No
1001	2	93	No
1001	3	241	No
1001	4	241	No
1001	5	93	Yes

[JPSeagull]

Thanks, atcodedog05 - it's close, but it helps build on it. Still looking to figure out how to evaluate each row within the Task ID group if it repeated not on itself, but only after a break. 

[gabrielvilella]

Hey @JPSeagull, for the error you are getting, this is related to the field names, you have leading and trailing spaces there which are not present on your multi row. I added a dynamic rename and removed those spaces. Second issue I was able to locate was your IFF expressing had the true and false statements swapped. Lastly I added a summarize to get that final table you mentioned.

[JPSeagull]

@gabrielvilella 

You asked: Why the first two KitID 93 are set to No when you have the same 93 within the same TaskID 1001? Same question for the KitID 241.

Task ID	Entry ID	Kit ID	Kit Repeated between Kit(s)?
1001	1	93	No
1001	2	93	No
1001	3	241	No
1001	4	241	No
1001	5	93	Yes

What we are trying to identify is if a Kit is used within an Task ID and put away that it is not brought out again after different Kit ID in the same event. That is, if 93 is used over two entries and put away for 241, then we expect not to see 93 returned to use in the same Task ID. We are not looking for how many lines in a row a kit is used. We are looking to see if a kit is repeated between other kits in the same Task. Does this explanation help?