Case Function

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
2.16K viewsFormulas and Functions
0

Here is a good one.
The case statement uses a test value, condition, and result. As I try to convert departments and accounts into a P&L line I uses combinations of vales to determine what line to put the data on.
How would I do what sql does. In SQL it looks like this

CASE
WHEN AccountsName = ‘Gross Sales’ THEN ‘Gross Sales’
WHEN AccountsName = ‘DISCOUNTS & CHARGEBACKS’ THEN ‘Discounts’
WHEN
left(DepartmentKey, 1 ) in ( ‘0’, ‘1’ )
and left(AccountsKey, 1 ) in ( ‘4’, ‘5’ )
THEN ‘Cost’
WHEN
LEFT(DepartmentKey, 3) = ‘221’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Customer Service’
WHEN
LEFT(DepartmentKey, 3) = ‘811’
AND AccountsKey = ‘41431’
THEN ‘Commissions’
WHEN
LEFT(DepartmentKey, 3) = ‘811’
AND AccountsKey = ‘53912’
THEN ‘Excise Tax’
WHEN
LEFT(DepartmentKey, 3) = ‘811’
AND AccountsKey = ‘53971’
THEN ‘Bad Debts’
WHEN
LEFT(DepartmentKey, 3) = ‘913’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Softgoods Product Development’
WHEN
LEFT(DepartmentKey, 3) = ‘915’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Hardgoods Product Development’
When
LEFT(DepartmentKey, 3) = ‘916’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Sourced Overhead’
WHEN
LEFT(DepartmentKey, 3) in ( ‘901’, ‘902’, ‘911’, ‘912’, ‘914’ )
AND LEFT(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Marketing’
WHEN
left(DepartmentKey, 3) in ( ‘411’, ‘421’, ‘431’ )
and left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Finance’
WHEN
[1Department_1].DepartmentName = ‘Administration’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Administration’
WHEN
[1Department_1].DepartmentName = ‘Human Resources’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Human Resources’
WHEN
left(DepartmentKey, 3) in ( ‘701’, ‘711’, ‘712’, ‘721’, ‘731’, ‘741’, ‘771’ )
and left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Hardgoods Product Development’
WHEN
[1Department_1].DepartmentName = ‘IT’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘IT’
WHEN
LEFT(DepartmentKey, 3) = ‘211’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Sales’
WHEN
DepartmentName = ‘SELLING EXPENSES’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Selling’
WHEN
LEFT(DepartmentKey, 3) = ‘611’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’, ‘6’ )
THEN ‘Warranty’
WHEN
LEFT(DepartmentKey, 3) = ‘511’
AND left(AccountsKey, 1) in ( ‘4’, ‘5’ )
THEN ‘Distribution’
WHEN AccountsName LIKE ‘Tax%’ THEN ‘Taxes’
WHEN
LEFT(DepartmentKey, 3) = ‘000’
AND AccountsName = ‘PROFIT SHARING’
THEN ‘Profit Sharing’
WHEN
–LEFT(DepartmentKey, 3) = ‘000’ And
AccountsKey in (
‘82001’,
‘82003’,
‘82004’,
‘82006’,
‘82007’,
‘81201’,
‘82011’,
‘81101’,
‘82001’,
‘89999’,
‘82014’,
‘62101’
)
THEN ‘Misc. Inc/Exp’
WHEN
LEFT(DepartmentKey, 3) = ‘000’
And AccountsKey in ( ‘82002’, ‘82016’ )
THEN ‘Interest Expense’
WHEN
LEFT(DepartmentKey, 3) = ‘000’
And AccountsKey = ‘82101’
THEN ‘Dividend Income’
ELSE ‘Unknown’

Edited question
1

There is a CASE function in the Quantrix function library that does something very similar… is that what you are looking for?