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 |
Solved! Go to Solution.
@hemant86
why Account#3333 is not included in the output?
@hemant86
Assuming there is a typo for account#3333. then we can try this.
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.
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 |
Thanks sazit for your response. That was a typo.Just responded to Qiu with corrected values
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
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
You should have mentioned me so I could notice earlier.
Hope I can get another accept mark from you😁