Only joining specific rows based on plus/minus sign

Hi,

I'm struggling with a task for my studies. To put it simply, I have two different Excel files as input data. One of them (Excel 1) contains account numbers and their corresponding category according to the german GAAP (generally accepted accounting principles). The second file (Excel 2) contains the account numbers and their balances. My aim is to join the two files based on account number, and aggregate the balances to see the sum of the individual GAAP-Items.

My problem:

In Excel 1, some accounts are listed two times. One of them would be correct if the balance is positive, the other one would be correct if the balance is negative.

See my attached image for an example. If the Account "X123123" has a positive balance, it belongs to "claims on banks", if the balance is negative it belongs to "liabilities to banks".

Since I join the two Excel files based on the Account number, and the Account "X123123" appears two times in Excel 1, it joins an examplary balance of EUR 500 two times, one time to the Assets, one time to Liabilities.

Is there a way to assign the Account to the right GAAP-Item based on the balance BEFORE joining it with Excel 2? I struggled here because I can only find the balance in Excel 2, but I need to know if the balance is positive or negative to assign it to Assets/Liabilites.

Glad for any suggestions! Thanks!

Example.PNG

Workflow

Datasets

Custom Tools

Accepted answers

All comments

atcodedog05

Hi @eldansky

Why dont you use a formula or filter tool split Assets and Liabilities

[amount]<0 // negative liability

else //positive asset

Hope this helps 🙂

atcodedog05

Hi @eldansky

Here is a workflow for the task. After splitting you can join.

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

balance.yxmd

eldansky

Hi @atcodedog05

thanks for your help, I still struggle to implement it into my workflow (it's my first time working with alteryx, sorry!)

I attached you a screenshot of my Join output, that shows a balance of 12.879,32 on account S110004. Since the balance is positive it should only keep row 9 (Forderungen an Kreditinstitute) and discard row 8. Unfortunately not every Account appears twice because some accounts are 100% assignable. So I figure I'd need a filter that checks if an account appears twice, and if so, check if the balance is positive or negative and delete the wrong row accordingly.

Sorry if my english fails to explain my problem properly 😄

Capture.PNG

atcodedog05

Hi @eldansky

Can you provide some sample data. Its really hard to work without that.

If you provide we will be happy to help 🙂

eldansky

I attached the sample data.

I removed unnecessary columns that I would discard in my workflow anyways via select tool.

See also attached a picture of my desired output data.

I've marked Account S110004 in both Excels as example. It has a positive balance in the"Account balances" excel.

It exists two times in the "chart of accounts", because depending on the balance only one Allocation is correct.

My workflow works pretty well, except that when I run it, the balance of account S110004 appears on both sides of the balance sheet (which is incorrect).

Thank you so much for your help and feel free to ask any further questions.

Cheers

account balances.xlsx

Chart of accounts.xlsx

Capture.PNG

atcodedog05

Hi @eldansky

Where exactly is the negative and positive sign

Based on your expected output here is a workflow. This considers only last row when there is duplicate

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

balance acc.yxmd

eldansky

Hi,

the positive/negative sign is in the account balances excel file in the column balance.

Is there any way to customize the "Sample" tool you used? In your workflow it says to keep the Last N rows (N=1). That works for some accounts that appear two times.