Creating Pivot Table in the middle of the flow

[MelGibson]

New to Alteryx and first time poster to any board 😉

I have a very large data set the files that are brought together can be over 700k rows and after I find the unique values I need to determine if the number has only one 2nd client number. This will further reduce the data. Currently I can reduce it to 275K rows, but with this find I then can reduce it to about 37K rows. Making this a much easier data set to work with. Currently I use pivot tables to narrow it down along with formulas, but it clocks and clocks. Just putting it all together has crashed my excel. I love Alteryx cause I can bring it all together in less than 2 minutes. 

I appreciate the help. I am attaching my Alteryx build and my excel current output on one tab and the output I am looking for on the other tab. 

I hope I did this right and I am posting in the right place

[JoeS · Accepted solution]

I think you just need a unique tool at the end.

Like the attached:

The output you added seems to have a State Number 2nd Client Number of

TX192517001

But I can't find that in the example you uploaded?

[MarqueeCrew]

@MelGibson ,

I would use a SUMMARIZE tool and GroupBy the 2nd client number and Count that field.  Next I would FILTER the results to Count = 2 (for those that have exactly 2).  Next I would JOIN the original data to this FILTER (True) output joining on 2nd client number.  UNCHECK all of the RIGHT fields.

Now you only get results with 2 rows.

Cheers,

Mark

[MelGibson]

Joe - You sir are a Rock Star!!!!! This perfect and I will mark this as solved. Thank you for the quick response. I learned something new, which is always good in my book. 

[JoeS]

---

@MelGibson wrote:

Joe - You sir are a Rock Star!!!!! This perfect and I will mark this as solved. Thank you for the quick response. I learned something new, which is always good in my book. 

---

Great stuff, I wasn't sure if I had missed something or not, with the fact that one record wasn't there.

Always happy to help!