# Case Function

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’