Grouping identical transactions

[ATeare]

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 #	Account	Debit/Credit	Amount
1	Debtor	Dr	114
1	Sales	Cr	-100
1	VAT	Cr	-14
2	Bank	Dr	50
2	Expense	Cr	-50
3	Debtors	Dr	50
3	Sale	Cr	-50
4	Debtor	Dr	228
4	Sales	Cr	-200
4	VAT	Cr	-28

The output needs to look like one of the following:

a)

Jnl #	Account	Debit/Credit	Amount	xType
1	Debtor	Dr	114	1
1	Sales	Cr	-100	1
1	VAT	Cr	-14	1
2	Bank	Dr	50	2
2	Expense	Cr	-50	2
3	Debtors	Dr	50	3
3	Sale	Cr	-50	3
4	Debtor	Dr	228	1
4	Sales	Cr	-200	1
4	VAT	Cr	-28	1

Or

xType	Account	Debit/Credit	Amount
1	Debtor	Dr	242
1	Sales	Cr	-200
1	VAT	Cr	-42
2	Bank	Dr	50
2	Expense	Cr	-50
3	Debtors	Dr	50
3	Sale	Cr	-50

I'd appreciate your help!

Thanks

[LordNeilLord]

Hey @ATeare,

Maybe this would be a good start for you

[danrh]

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

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!

[ATeare]

Thank you ! This helped a lot!

[ATeare]

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