Forum Discussion
Solved
Create A Custom Column with and IF Statement that references values in a separate table.
Good afternoon, I have a table with 25 columns to identify different diagnosis codes. I would like to create a custom column that flags whether any of the diagnosis codes (180) in another table are...
Trying to flag the Trigger Diags sounds like? Something along these lines should work. You would add this as a calculated column on the Claim table.
Trigger_Diagnosis = SWITCH ( TRUE(), NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code1] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code2] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code3] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code4] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code5] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code6] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code7] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code8] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code9] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code10] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code11] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code12] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code13] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code14] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code15] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code16] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code17] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code18] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code19] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code20] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code21] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code22] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code23] ) ), "Y", NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code24] ) ), "Y" )
Trying to flag the Trigger Diags sounds like? Something along these lines should work. You would add this as a calculated column on the Claim table.
Trigger_Diagnosis =
SWITCH (
TRUE(),
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code1] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code2] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code3] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code4] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code5] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code6] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code7] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code8] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code9] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code10] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code11] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code12] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code13] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code14] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code15] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code16] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code17] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code18] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code19] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code20] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code21] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code22] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code23] ) ), "Y",
NOT ISBLANK ( LOOKUPVALUE ( Trigger_Diag[Diagnostic_Code], Trigger_Diag[Diagnostic_Code], Claim[Diagnostic_Code24] ) ), "Y"
)
Thanks so much for your help and quick response. I will definetely try your solution.