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.

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.

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.

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.

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