Only joining specific rows based on plus/minus sign

[eldansky]

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!

[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 😀👍

[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 😄

[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]

Sure, I will prepare it asap.

I'll have to edit it a little bit since it's real data from a company I use for my paper. I'll do some edits and then post the two Excel files.

[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

[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 😀👍

[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.

Example Account S110004. It's positive according to account balances, therefore it has to be categorized as Forderungen an Kreditinstitute.

The Same goes for account S110600:

The Problem is, that  the Sample tool always keeps the last row, therefore works for S110004 but puts S110600 to P 01 instead of A 03.

They should both be Aktiva/A 03/ Forderungen an Kreditinstitute.

 

Of course it'd be possible to manually assign the accounts that appear two times,  but the point of my workflow would be to avoid that 🙂 

Again thanks for your time and help, really appreciate it.

[atcodedog05]

Hi @eldansky 

Great now i have more idea. So we can use sort to make A 03 come last.

Output:

Workflow:

Please check and let me know🙂