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. TransactionCodeBExpenseAmountCRight_ExpenseAmount201992100010006237001-400004400201992100010006237001-400005400201992100010006237001-400006400201992100010006237002-400004400201992100010006237002-400005400201992100010006237002-400006400201992100010006237003-400004400201992100010006237003-400005400201992100010006237003-40000640020199210001000653600130002-3020199210001000653600130006-30201992100010006536004-3000330201992100010006536004-300073020199210001000653600530002-3020199210001000653600530006-30201992100010006536008-3000330201992100010006536008-300073020199210001000733300190002-90201992100010007333004-900039020199210001000733300554006-54201992100010007333008-5400754201992100010008598001530.22002-530.22201992100010008598004-530.22003530.222019921000100085980051974.96006-1974.96201992100010008598008-1974.960071974.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. TransactionCodeBExpenseAmountCRight_ExpenseAmount201992100010006237001-400004400201992100010006237002-400005400201992100010006237003-400006400

Get unique rows based on two columns where the values in each column does not repeat

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

Datasets

Data Investigation

Accepted answers

Qiu

@timothyyeo

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.

0501-timothyyeo.yxmd

AkimasaKajitani

This workflow will work well.

But Assumptions is that the unique record count are the same between Field B and C.

If it is not the same, you have to go to Iterative Macro root.

UniqueRow_AK.yxmd

All comments

Qiu

@timothyyeo

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.

0501-timothyyeo.yxmd

AkimasaKajitani

This workflow will work well.

But Assumptions is that the unique record count are the same between Field B and C.

If it is not the same, you have to go to Iterative Macro root.

UniqueRow_AK.yxmd

timothyyeo

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.

timothyyeo

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703