3 Replies Latest reply on Sep 28, 2016 1:34 PM by Joshua Milligan

    How to create: If ID is only A, then A, else B.

    Anthony Speziale

      Struggling with a problem I think I'm making more difficult than it needs to be.

      In short, if an ID has a category of "A" in one line item, and "B" in another, it should be classified as B when a distinct list is pulled.

       

      Thanks in advance, data examples below. If it helps, I have the option of creating a view in Teradata, then access from Tableau.

       

      Data looks like this:

       

      ID
      Category
      1001A

      1001

      A
      1002A
      1002B
      1003A
      1004B

       

       

      Need to look like:

       

      ID
      Category
      1001A
      1002B
      1003A
      1004B
        • 1. Re: How to create: If ID is only A, then A, else B.
          Joshua Milligan

          Anthony,

           

          The key is that you want to work at the ID level.  If the view you are creating is using ID, then you might consider using simple aggregate or table calculations.

           

          But more likely, if you want the flexibility to work at any level of detail in the view, then use a Level of Detail calculation fixed to the ID level.  The code would be something like:

           

          {FIXED [ID] : MAX([Category])}

           

          What this will do is return the maximum category for each ID.  If it is only A then the MAX is A.  If it is A and B then the max is B.

           

          Be aware that FIXED level of detail calculations are evaluated across the entire context of the data (usually the entire data set unless you use a context filter).  This means that even if you filter to a subset of data (e.g. to a certain date) where the Category is only A, but B exists within the entire context you'll still get B as the result.

           

          Hope that helps!

          Joshua

          • 2. Re: How to create: If ID is only A, then A, else B.
            Anthony Speziale

            Thanks for your reply Joshua,

             

            That method does work for me. although, MIN() was the correct choice (backwards in example).

            My initial concern, is that using MAX versus MIN may not be full proof.

             

            Although, I haven't been able to prove that yet!

            Marking your answer correct, thanks!

             

            Anthony

            • 3. Re: How to create: If ID is only A, then A, else B.
              Joshua Milligan

              Anthony,

               

              You're welcome!  If you are concerned that MAX or MIN might fail based on data, then you can get more specific with the calculation:

               

              {FIXED [ID] : MAX(IF [Category] = "B" THEN "B" END)}

               

              will return B for any category (at the row level) that has a B record in the data, even if there is an A record or C record (thus B wouldn't be the overall MAX).  If there's not a B record in the data from the category, then the result will be NULL, so you can extend the calculation to then give you the "A" value if the "B" is null:

               

              IFNULL({FIXED [ID] : MAX(IF [Category] = "B" THEN "B" END)}, {FIXED [ID] : MAX(IF [Category] = "A" THEN "A" END)})

               

              That will give you "B" if there is a B record (with or without an A record) otherwise "A" if there is an "A" record (and implicitly NULL if there is neither).

               

              Hope that helps!

              Joshua

              1 of 1 people found this helpful