Identifying which rows are missing

[JioLessard]

Hello Alteryx community!

I'm new to using Alteryx Designer so this question might be trivial, sorry if it is!

So, here's what I'm trying to do:

I have a list of 14 unique keys, here's a sample:

I also have a table containing hundreds of records for items. An item can be inside this table for a maximum of 14 rows (1 for each of the 14 unique keys "Tempo Key LOC+SoldTo"). 

With a Summarize tool, I've been able to idenfity items that do not have 14 rows, as an example, here, the item "44000875" is only found 9 times:

Now, I need to somehow find exactly which 5 "Tempo Key LOC+SoldTo" are missing, and I need to do that for every items.

How would I go about doing such a thing?

Thank you very much for your time,

JioLessard

[afv2688]

Hello @JioLessard ,

For that you can use a Join tool. Use the left to select the file on the end (after all the process) and the right one for the input you used.

Select then the variables to avoid duplicates.

The ones appearing on the right output of the join tool will be the ones missing.  🙂

Hope I explained myself clearly

Regards

[Martyn · Accepted solution]

Filter your Summary output for CountDistinct < 14.

Create a list of the 14 "Tempo Key LOC+SoldTo" possibilities as an input.

Append this to your filtered Summarize output.

Join to your original file (on "item"). L input should be the appended summarize output. The L output should then give you a list of what is missing for each item code.