Need help

[ashaik1]

Hello - I have the below data and i would like to bring in an additional column next to 'Nature'  based on the account and its nature.

Example: Asset and Liability is Balance Sheet(B/S) and Revenue and Expense is Income Statement (IS). Journal entry 1 has both Asset and Liability so in my new column it should show me B/S only as a comment. 

Journal entry 3 has both Asset and Revenue so in my column it should give me B/S and I/S.

Kindly help

Journal Entry	Account	Nature
1	100000	Asset
1	200000	Liability
2	400000	Revenue
2	500000	Expense
2	700000	Expense
3	100000	Asset
3	400000	Revenue

[aatalai · Accepted solution]

Ok I would start with using the formula tool and say if [Nature]="asset" or [Nature]="Liability" then "B/S" else "I/S" endif

Then use the summarise tool group by journal entry and count distinct of the new the column generated.

Then join this with the output of the summarisation tool with the output from the formula tool joining on journal entry. 

Then add an other formual tool with If distinct count =1 then [column generated earlier] +"only" else "Both B/S and I/S"

@ashaik1 hope that helps

[Qiu · Accepted solution]

@ashaik1 
If we are going to write a more than 2 lay of conditional statement, I always prefer a mapping file.