This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
Thank you ! This helped a lot!
Do you perhaps have the SQL query for this for an in-DB query?