0 Replies Latest reply on Apr 18, 2013 10:33 AM by Rick Graham

    Trying to create a conditional calculation that checks for an error and returns a value

    Rick Graham

      Good afternoon everyone.  I am trying to convert a calculation that I am using in Excel 2007 for use as a filter in Tableau and I am running into some difficulty.  The Excel caluclation looks like this:

       

      =IF(K1789="Exclude - Not RE","Not RE",IF(P1789="870","IQS",IF(T1789="DGA","Portfolio",IF(J1789="FF",IFERROR(VLOOKUP(O1789,References!$A$2:$B$291,2,FALSE),"Lending"),VLOOKUP(O1789,References!$A$2:$B$291,2,FALSE)))))

       

       

      It is a pretty straight forward tansformation of data.  This is how the calculation breaks down:

       

      - If the Segment (K1789) is equal to “Exclude-Not RE” Then return “Not RE”;

      - If the Ledger Code  (P1789) is equal to “870” Then return “Agency IQS”;

      - If the System (T1789) is equal to DGA Then return “Portfolio”;

      - If the Passback (J1789) is equal to "FF", and if there is an error in the category code (O1789) then return "Lending" otherwise look through the list of category codes and match it to the value in the passback field return the value associated with that code.

       

      So, the I wrote the following calculation in Tableau for the first three conditions and it works great:

       

      IF [LOB Segment] = 'Exclude - Not RE' THEN 'Not RE' ELSEIF [GL Code] = '870' THEN 'Agency IQS' ELSEIF [System] = 'DGA' THEN ' Portfolio' ELSE 'Other' END

       

      The problem that I am having is figuring out the fourth condition that is listed specifically trying to address the condition if there is an error.  I tried the following which did not work at all:

       

      IF [LOB Segment] = 'Exclude - Not RE' THEN 'Not RE' ELSEIF [GL Code] = '870' THEN 'IQS' ELSEIF [System] = 'DGA' THEN 'Portfolio' ELSEIF ([Passback] = 'FF' AND ([Center] = '0000A31' OR [Center]='0000A41' OR [Center]='0000A42' OR [Center]='0000A43'

      OR [Center]='0000A69' OR [Center]='000A064' OR[Center]= '000A074' OR [Center]='000A194' OR [Center]='000A117'

      OR [Center]='000B871' OR [Center]='000B872' OR [Center]='000B877' OR [Center]='000B991' OR [Center]='000B992'

      OR [Center]='000B994' OR [Center]='000B998' OR [Center]='000B025' OR [Center]='000B027' OR [Center]='000B029'

      OR [Center]='000C039' OR [Center]='000C041' OR [Center]='000C045' OR [Center]='000C046' OR [Center]='000C047'

      OR [Center]='000D048' OR [Center]='000D050' OR [Center]='000D052' OR [Center]='000D053' OR [Center]='000D054'

      OR [Center]='000E070' OR [Center]='000E079' OR [Center]='000E094')) THEN 'Lending' ELSE 'Other' END

       

      If anyone has any suggestions or has tackled a similar issue in the past I would be grateful for any guidance.  Thanks in advance.