Filter on field with lists/multiple selections

[michelle22]

Hi,

I need to create a filter that will be able to select options within a list.

For example, if my data is:

Column

Blue, Black, Red

Blue

Red, Black

Black, Red

Red

I want to be able to create a filter that will select each of the three colors. I have tried to do this using a WildMatch, but it is clear that each row is only counted once, so for example 'Red, Black' will only be 'Red' as 'Red' is listed in the set analysis first.

=IF(WildMatch([Column],'*Red*'), 'Red',
IF(WildMatch([Column], '*Black*'), 'Black',
IF(WildMatch([Column], '*Blue*'), 'Blue')))

I have also tried created new fields in the data load editor, but encounter the same issue:

Data Load Editor:
IF(WildMatch([Column],'*Red*'), 'Yes','No') as [Red] ...

Filter:
=If(Match([Red],'Yes'),'Red' ....

Is this at all possible? Just to note, my data consists of lists of long phrases with multiple commas/no standard breaks.

[Or · Accepted solution]

This would typically be handled in the script by using SubField().

Load Subfield(Column,',') as Color

From YourTable;

[mato32188]

Hi @michelle22 ,

in load script create another table to filter unique color like:

tmp:
load * Inline [
Column
'Blue,Black,Red'
'Blue'
'Red,Black'
'Black,Red'
'Red'
];

tmp2:
load
Column,
subfield(Column,',') as "Distinct Color Column"
Resident tmp;

Use Distinct Color Column in your filter pane.

br

m