Filter pane value list with calculated values from delimited dimension

[epar5lcd]

I have a dimension with many delimited listed values, lets call them "Ingredients", for individual items. The same eight ingredients are randomly present for 30k items.

Item	Ingredients
A	Sugar;
B	Milk; Sugar;
C	Egg; Water;
D	Flour; Milk;  Water;
E	Salt; Sugar; Yeast;
F	Molasses;

Using [Ingredients] as a dimension in the filter pane results in 96 filter choices. I would like to create a filter pane where the user can only select the 8 possible available ingredients.

Selecting 'Sugar' in the filter pane should return Items A, B and E in above example.

Selecting 'Sugar' AND 'Milk' returns A, B, D and E. 

Using nested WildMatch does not yield the expected results but got me closest to my goal:

=IF(WildMatch([Ingredients],'*Egg*'),'Egg',
IF(WildMatch([Ingredients],'*Flour*'),'Flour',
IF(WildMatch([Ingredients],'*Milk*'),'Milk',
IF(WildMatch([Ingredients],'*Molasses*'),'Molasses',
IF(WildMatch([Ingredients],'*Salt*'),'Salt',
IF(WildMatch([Ingredients],'*Sugar*'),'Sugar',
IF(WildMatch([Ingredients],'*Water*'),'Water',
IF(WildMatch([Ingredients],'*Yeast*'),'Yeast'))))))))

Above function in the filter pane appears to only selects Item A if 'Sugar' is chosen, and selects Items A and B if 'Sugar' and 'Milk' are chosen. The undesired calculations are resulting from the order of operations in the IF statement. Applying a filter on 'Egg' will include all items that have eggs plus anything else. This is the desired behavior. Applying a filter on on 'Flour' only executes if 'Egg' is false so items with eggs are excluded. This is the undesired behavior. Removing the condition =IF(WildMatch([Ingredients],'*Egg*'),'Egg', allows 'Flour' to calculate as desired but then you can no longer pick 'Egg' in the same filter pane. I would like to avoid creating 8 different filter panes.

I was hoping that using Pick() would result in the desired behavior. It doesn't but the code is cleaner:

=Pick(WildMatch(
[Ingredients],'*Egg*', '*Flour*', '*Milk*', '*Molasses*', '*Salt*','*Sugar*', '*Water*', '*Yeast*')+1,
'Unknown', 'Egg', 'Flour', 'Milk', 'Molasses', 'Salt', 'Sugar', 'Water', 'Yeast')

Any help to get the filter pane to yield the correct results would be greatly appreciated. I have tried various other combinations of ValueList() and Aggr() but making a selection is doesn't actually filter any data or the function doesn't appear to be supported by a filter pane.

A different approach I am exploring is using SubField() in the load editor. It may be necessary to create a secondary table and LOAD DISTINCT to unpack the data. Not quite sure how to do this though.

Thanks!

Sample data attached.

[Cameron-R]

Did you ever find the solution to this?

[TauseefKhan]

Hi @Cameron-R,

Check with this:

// Normalize the Ingredients field
IngredientsNormalized:
LOAD
    Item,
    Trim(SubField(Ingredients, ';')) AS Ingredient
Resident YourOriginalTable;

**********Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.******** 

[Cameron-R]

Thanks for your response @TauseefKhan . Your solution worked to separate the delimited values in the filter pane, but I'm only able to filter by one field value successfully at a time. Is there a way to filter by all that apply within the filter pane?

[TauseefKhan]

Apply the above logic in scripting.
Is this not allowing you to select other field value at same time in filter?

[MeehyeOh]

Hi, @Cameron-R 

Try this.

The test was with the sample data described in the post.

I used "|" instead of ";" as a delimite.

1. Load script

Test:

Load * Inline [

Item, Total_Ingredient

A, Sugar|

B, Milk| Sugar|

C, Egg| Water|

D, Flour| Milk|  Water| 

E, Salt| Sugar| Yeast|

F, Molasses| ];

Ingredient:

Load

Total_Ingredient,

    Trim(SubField(Left(Total_Ingredient,Len(Total_Ingredient)-1), '|')) AS Ingredient

Resident Test;

2. visualization

1) Selecting 'Sugar'

2) Selecting 'Sugar' AND 'Milk' 

[Cameron-R]

Thanks, @MeehyeOh . This solution works for my application as well.