8 Replies Latest reply on Nov 19, 2013 10:16 AM by Michelle Eich

    calculated field with string output based upon a row attribute and a column attribute

    Michelle Eich


      Hi there,

      I originally posted a question here:  calculated field with string output along customer per customer segment The calculated field provided by Jonathan Drummey was:

      IF TOTAL(SUM([Number of Records])) > 0 THEN

          IF ISNULL(LOOKUP(SUM([Number of Records]),0)) THEN 1

            

          ELSE

             0

          END

      END

      It worked as I had hoped and was very excited but then I learned I didn't fully understand the request of my user.    This question plays off of that request but goes a step further.

      I've attached a dummy workbook in hopes of finding the help I need.

       

      I have Agent, Company and LOB(Line of business) in my data set.

       

      If an agent has at least 1 record with a specific company and LOB, it should show as E - Existing.   But now I want to determine those relationships that are available.

       

      Notice those with any num of records in the "currently" view change to E's in the "desired output" view.  Again simple enough.

       

      However, an LOB for a particular company can only be marked as Available if there exists an E somewhere in that company row AND exists an E somewhere in that LOB column. Meaning the agent already does business in that LOB with another Company AND already does business with that Company.  Have I confused you? I'm pulling my hair out on this one.

       

      So because Jeff does 123c (LOB) business already AND  has a relationship with company GHI already, then LOB 123c should be marked as AVAILABLE for Company GHI.

       

       

       

       

       

      Currently
        (num of records)
      Desired Output
      LOBLOB
      AgentCompany123a123c456b789cAgentCompany123a123c456b789c
      JeffABC1JeffABCEA
      GHI1GHIAE
      PaulABC8PaulABCEA
      DEF4DEFEA
      GHI1GHIAE