4 Replies Latest reply on Aug 1, 2018 4:49 AM by Priyanka Ka

    Create a vlookup like calculated field using data blending

    Priyanka Ka

      Hello everyone,

       

      I am working on the accounts data of a certain financial institution.

      AccID- unique Acc ID

      Rolled Over from- Contains the AccID if the account is rolled over from an older account.

       

      To calculate:

      1. Has been rolled over: A binary field that is true if an account has been rolled over. to another account.

      2. Open Accounts: Is true if an account is neither Closed(Withdrawal date IS NULL) nor Rolled Over.

       

      Solution till now:

      My aim is to segregate the accounts as Closed, Open and Rolled over. Once segregated, these values need to be used in further calculations.

      1. Closed: If 'Withdrawal date' <> NULL - already calculated

      2. Rolled Over: The accounts for which AccIDs that are present in the 'Rolled over from' column (anywhere) are 'Rolled over' accounts.

      I duplicated my data source and linked the two data sources to visualize which AccIds have been rolled over. However, this does not resolve my requirement.

      What I need to do is to create another calculated field called ' Has been rolled over', a binary value that indicates which AccIDs have been rolled over.

      Please note that creating something like this in the duplicate data source

      (

      if NOT ISNULL([Rolled over from]) THEN 1

      END

      ) did not work for me, because it ended up indicating the accounts that have been rolled over from another account and not the other way round.

      3. Open Accounts: I need to create another column with something like:

       

      if (ISNULL(closed accounts) AND ISNULL(Has been Rolled  Over))

      THEN 1

      END

       

      but with my calculation, the 'Has been rolled over' is actually calculating if an account is a roll-over account and not if an account has been rolled over.

       

      I am fairly new to Tableau and using Data blending for the first time. For better understanidng, I am attaching the data source file that has a 'result required' tab.

      The two fields I need to calculate are highlighted in yellow.

      Will greatly appreciate any pointers in this regard.

       

      Thanks!