Hi All,
I'm stuck on a problem in alteryx designer that I hope someone in this community can help with?
I'm trying to get unique rows from the dataset below based on unique values in columns TransactionCode ,B, and C where values in B and C do not repeat.
TransactionCode | B | ExpenseAmount | C | Right_ExpenseAmount |
201992100010006237 | 001 | -400 | 004 | 400 |
201992100010006237 | 001 | -400 | 005 | 400 |
201992100010006237 | 001 | -400 | 006 | 400 |
201992100010006237 | 002 | -400 | 004 | 400 |
201992100010006237 | 002 | -400 | 005 | 400 |
201992100010006237 | 002 | -400 | 006 | 400 |
201992100010006237 | 003 | -400 | 004 | 400 |
201992100010006237 | 003 | -400 | 005 | 400 |
201992100010006237 | 003 | -400 | 006 | 400 |
201992100010006536 | 001 | 30 | 002 | -30 |
201992100010006536 | 001 | 30 | 006 | -30 |
201992100010006536 | 004 | -30 | 003 | 30 |
201992100010006536 | 004 | -30 | 007 | 30 |
201992100010006536 | 005 | 30 | 002 | -30 |
201992100010006536 | 005 | 30 | 006 | -30 |
201992100010006536 | 008 | -30 | 003 | 30 |
201992100010006536 | 008 | -30 | 007 | 30 |
201992100010007333 | 001 | 90 | 002 | -90 |
201992100010007333 | 004 | -90 | 003 | 90 |
201992100010007333 | 005 | 54 | 006 | -54 |
201992100010007333 | 008 | -54 | 007 | 54 |
201992100010008598 | 001 | 530.22 | 002 | -530.22 |
201992100010008598 | 004 | -530.22 | 003 | 530.22 |
201992100010008598 | 005 | 1974.96 | 006 | -1974.96 |
201992100010008598 | 008 | -1974.96 | 007 | 1974.96 |
For eg. for rows with TransactionCode "201992100010006237", the correct result is shown below. If you see, I'm not looking for unique combinations of TransactionCode, B, C. Instead, it has to be ensured that individually taken, the values in B and C should not repeat. In the example below, once 001 in B has been returned, it should not be returned in another row in the result. Similarly, once 004 in C has been returned, it should not be returned in another row.
Appreciate if you can let me know how this can be done in designer. Thanks for your time and help in advance.
TransactionCode | B | ExpenseAmount | C | Right_ExpenseAmount |
201992100010006237 | 001 | -400 | 004 | 400 |
201992100010006237 | 002 | -400 | 005 | 400 |
201992100010006237 | 003 | -400 | 006 | 400 |
Solved! Go to Solution.
I think this should work but did not know what to do if the B and C are not have same number of unique counts.
Hi @Qiu ,
Thanks for the fast reply. Really appreciate it. I don have transactions where B and C do not have the same number of unique counts, and the expenseamounts also do not match up so neatly, but I feel that will be too complicated done in alteryx so I didn't bother to post it.
Hi @AkimasaKajitani ,
Thanks for the fast reply. Both yours and @Qiu 's solutions work. I do have transactions where B and C do not have the same number of unique counts, and the expenseamounts also do not match up so neatly, but I feel that will be too complicated done in alteryx so I didn't bother to post it.
@timothyyeo
Glad to help and thank you for the accept mark,