community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Grouping identical transactions

Highlighted
Meteoroid

Hello community

 

I'm trying to create an additional field to group similar transactions in a large data set. See example below:

 

From the source data, I want to create a new column which will allocate an additional reference (xType) based on the combination of Accounts and Debit/Credit for each journal, i.e. if Journal 1 and 4 has the same account order and Dr/Cr combination, they should both be assigned xType = 1. If not, the next combination should be xType = 2, 3 etc.

 

Source data:

 

Jnl #AccountDebit/CreditAmount
1DebtorDr114
1SalesCr-100
1VATCr-14
2BankDr50
2ExpenseCr-50
3DebtorsDr50
3SaleCr-50
4DebtorDr228
4SalesCr-200
4VATCr-28

 

The output needs to look like one of the following:

 

a)

Jnl #AccountDebit/CreditAmountxType
1DebtorDr1141
1SalesCr-1001
1VATCr-141
2BankDr502
2ExpenseCr-502
3DebtorsDr503
3SaleCr-503
4DebtorDr2281
4SalesCr-2001
4VATCr-281

 

Or

 

xTypeAccountDebit/CreditAmount
1DebtorDr242
1SalesCr-200
1VATCr-42
2BankDr50
2ExpenseCr-50
3DebtorsDr50
3SaleCr-50

 

 

I'd appreciate your help!

 

Thanks

Alteryx Certified Partner

Hey @ATeare,

 

Maybe this would be a good start for you

Grouping.PNG

 

Pulsar

Here's how I would go about getting to either result:

image.png

It's the same basic concept for either option - concatenate the fields you want to compare first, then Summarize the data to see where the matches are.  Hopefully this gets you going in the right direction!

Meteoroid

Thank you ! This helped a lot!

Meteoroid

Do you perhaps have the SQL query for this for an in-DB query?

Labels