Find How many Employees are using the same account number

[hemant86]

HI All,

I need to pull a report of how many employees are using a single account at present date. Please find the sample table below. If I had to do it on excel the I would put a filter on column Account Number and if the count of rows is >=2 then I would pull that data. Any help will be greatly appreciated.

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019
600	325790	Y1K	3333	09/24/2019
700	325790	Y1K	3333	10/11/2019

expected output

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019

[Qiu]

@hemant86 
why Account#3333 is not included in the output?

[Qiu]

@hemant86 
Assuming there is a typo for account#3333. then we can try this.

[sazit]

Hey Hemant, 

Based on your question, if you want to filter for account numbers that appear twice or more than twice, the account 3333 should also be filtered through? Do let me know.

But if it is the case that you simply want to filter for records with account numbers appearing more than once then you would use a summarize tool and group by account number while also counting the account number. Then filter for count > 1 and join with the main data stream taking. Find below an example workflow. Hope this helps.

[hemant86]

Hi Qiu,

Sorry my bad. Let post the sample data again. So there can be duplicate rows as well.

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019
600	325790	Y1K	3333	09/24/2019
600	325790	Y1K	3333	10/11/2019

OutPut

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019

[hemant86]

Thanks sazit for your response. That was a typo.Just responded to Qiu with corrected values

[Qiu · Accepted solution]

@hemant86 

So the Empl ID has to be unique.

[sazit]

@hemant86 

You just have to group by the fields you want to be unique and only select the ones that come up once and join back on both of them too. Attached is the sample wf

[hemant86]

Thanks for the quick response Qiu. I'm new to Alteryx and might be asking armature questions. Is it possible to get the output in the below format.  

Input remains the same.

Expected Output:

Record No	Account Number	Count-Emp	Emp ID1	Emp ID2	Emp ID3
1	1111	2	100	200
2	2222	3	300	400	500

Emp ID column should be scalable since the count will keep changing

[Qiu]

@hemant86 

You should have mentioned me so I could notice earlier.
Hope I can get another accept mark from you😁