Hello - Taking the Impact column as a base in consideration with the Account/Department/Intercompany i need to derive the Journal type column.
For example: Journal entry 1, 4, 5 are B/S Only but having different scenarios.
Except the Journal 5 scenario the intercompany code may exist or may not exist in the other journal entries
This is how i need to define the Journal type. Please help
Journal Entry | Account | Department | Intercompany | Nature | Impact | Journal type |
1 | 100000 | 12345 | DEF | Asset | B/S Only | B/S reclass or GND reclass |
1 | 200000 | 34567 | Liability | B/S Only | B/S reclass or GND reclass | |
2 | 400000 | 89111 | Revenue | IS Only | I/S reclass or GND reclass | |
2 | 500000 | 12134 | Expense | IS Only | I/S reclass or GND reclass | |
2 | 700000 | 15167 | Expense | IS Only | I/S reclass or GND reclass | |
3 | 100000 | 18192 | GHI | Asset | B/s and IS | MAN PL ADJ |
3 | 400000 | 21223 | Revenue | B/s and IS | MAN PL ADJ | |
4 | 100000 | 99999 | Asset | B/S Only | DEPT Transfer | |
4 | 100000 | 88888 | Asset | B/S Only | DEPT Transfer | |
5 | 100000 | 12345 | ABC | Asset | B/S Only | Intercompany transfer |
5 | 100000 | 12345 | Liability | B/S Only | Intercompany transfer |
Hi @ashaik1 ,
I tried to follow your expected outcome. I hope this may help.
Worklfow
Formula tool 1
sameAccount = [CountDistinct_Account]=1
sameDepartment = [CountDistinct_Department]=1
Formula tool 2
Journal type =
IF ![sameAccount] AND ![sameDepartment] AND [Impact] = "B/S Only"
THEN "B/S reclass or GND reclass"
ELSEIF ![sameAccount] AND ![sameDepartment] AND [Impact] = "IS Only"
THEN "I/S reclass or GND reclass"
ELSEIF [Impact] = "B/s and IS"
THEN "MAN PL ADJ"
ELSEIF [sameAccount] AND ![sameDepartment] AND [CountNonNull_Intercompany] = 0
THEN "DEPT Transfer"
ELSEIF [sameAccount] AND [sameDepartment] AND [CountNonNull_Intercompany] = 1
THEN "Intercompany transfer"
ELSE Null()
ENDIF
Output