-
1. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
Taking a data set that looks like:
ID Color 01 Red 02 White 03 Blue 04 Red 05 White 06 Blue 07 Red 08 White 09 Blue 10 Red
you can use custom SQL like:
SELECT [data#txt].[ID] AS [ID],
[data#txt].[Color] AS [Color],
[data#txt].[Color] AS [Filter]
FROM [data#txt]
UNION ALL
SELECT [data#txt].[ID] AS [ID],
[data#txt].[Color] AS [Color],
"Red/Blue" AS [Filter]
FROM [data#txt]
WHERE [data#txt].[Color] = "Red"
OR [data#txt].[Color] = "Blue"
UNION ALL
SELECT [data#txt].[ID] AS [ID],
[data#txt].[Color] AS [Color],
"Red/White/Blue" AS [Filter]
FROM [data#txt]
WHERE [data#txt].[Color] = "Red"
OR [data#txt].[Color] = "White"
OR [data#txt].[Color] = "Blue"
to shape the data, repeating rows to enable you to filter the way you want.
When you show the quick filter, you will want to disable "All" from the customize menu, and change it to a single select filter, like in the attached workbook.
-
2. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
hello joemako - I have been working with tzimmerman and we are both a bit overwhelmed by your sql code listed above since neither of us have experience with this code. I am aware that we can embed your code into the data connection but we were hoping to siimply add to the formula below which we can use in a calcualted field. Is there a way to simply add 1 or 2 lines to achieve our objective?
Also, we are both currently using Tableau 5.1 so we can not access your attached workbook(which uses Tableau 5.2)
IF [color]="Red" THEN "RED"
ElseIF [color]="White" THEN "WHITE"
ElseIF LEFT [color]="Blue" THEN "BLUE"
end
-
3. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
Joe's custom SQL solution is probably your best bet in this case as long as you take care to avoid double counting. For example, if you use a multi-select filter instead of single-select, and allow both "Red" and "Red/Blue" rows, you'll get double copies of each red row.
You essentially want to use conditions to define your filter choices instead of using a simple list of values. One way to do that is to define calculated fields that you can use for your filter. That works well when you have one complex filter forumula that partitions rows into 2 or more categories. It's less easy to use when rows can belong to more than one category.
For example, you can define [red_or_blue] as
[color]="RED" or [color]="BLUE"
. Then you can use [red_or_blue] to make a checkbox quick filter. The problem with this solution is that you can end up with a list of overlapping filters, and the user has to understand how they all interplay. If the [red_or_blue] filter is set to false and the [red_white_blue] filter is set to true, you'll only see white rows, but if they are both set to true, you won't see any white rows.