Executing the advanced search with more than one wildcard

[mfrancke]

From a table with over 4000 entries, I would have to find those that contain either the string *name1* OR the string *name2* OR 'name3'.
The table has the following values:
Value1: Name1/Name2
Value2: Name3/Name4
Value3: Name1/Name3
Value4: Name4/Name2
Value5: Name3/Name3
All combinations are possible, whereby each name can be at the beginning or end of the string.
Name1 Name2 finds value1 because the value starts with Name1.
Name2 does not find a result.
I would actually have to search for *Name1* *Name3* *Name4*. Unfortunately, this does not work with the wildcards. How can I combine several searches with wildcards like a OR combination?

Many thanks for your help.
Regards

Matthias

[Chanty4u]

Try this 

WildMatch(Value, '*Name1*', '*Name2*', '*Name3*')

[Antoine04]

Use the function WildMatch.

something like :

WildMatch(YourField, *Name1*, *Name3*, *Name4*)

[TauseefKhan]

Hi @mfrancke 

The WildMatch function can be used to match strings to multiple patterns with wildcard characters.

Using a Calculated Dimension in a Chart: To create a dimension that filters based on your criteria, you can write an expression in the chart's dimension or script:

=If(WildMatch(Value, '*Name1*', '*Name2*', '*Name3*'), Value)

Filter Object: You can also create a filter using the WildMatch function directly in your filter pane:

=WildMatch(Value, '*Name1*', '*Name2*', '*Name3*')

Applying the Function in Load Script:

Loading Data: When loading data, if you need to filter the dataset to include only rows that meet your wildcard criteria:

TableName:
LOAD *,
If(WildMatch(Value, '*Name1*', '*Name2*', '*Name3*'), 1, 0) as MatchFlag
FROM DataSource;

WHERE MatchFlag = 1;

or

TableName:
LOAD *,
If(WildMatch(Value, '*Name1*', '*Name2*', '*Name3*'), 'Matched', 'Unmatched') as MatchStatus
INLINE [
Value
Name1/Name2
Name3/Name4
Name1/Name3
Name4/Name2
Name3/Name3
];

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

[mfrancke]

Thanks, I have found a slightly easier way. I have entered (*name1*|*name2*|*name3*) in the search field. So I can easily select the entries found.
Now I need to know how to explicitly exclude one of the entries.
If the value of the entry is Name1/Name3 I want to define a search that includes every Value except  everything that contains Name3 (like * NOT Name3).
And I need to know how to connect the search in two dimensions with an OR link. Search all entries that contain the value *Name1* in table 1 OR the value >1000 in table 2. Does that work?

[marcus_sommer]

In general you could connect n conditions, maybe like:

{< value = {("*name1*"|"*name2*"|"*name3*")}-{"*xyz*"}>}

{< dim1 = {"*xyz*"} > + < dim2 = {">1000"}>}

There are a lot of ways to define AND and/or OR connections between conditions and it's usually not a technically challenge else a logical ones to set the right connection in the right order.

,  = AND
|  = OR
+ = OR
*  = AND
/  = XOR
-  = NOT

and with a () wrapping/nesting the order could be adjusted.

Before diving deeper in the complexity to find the right / most suitable syntax for the wanted combination of conditions I suggest to consider to split the search-string into n columns (maybe categorizing them) and/or records (per subfield() or similar) and on top of it any counting/sorting and/or merging again to simplify the measurements in the UI.

[mfrancke]

Thank you for your detailed description.
I have been able to produce a result with WildMatch and a simple search, but I fail with excluding searches.
An example:
=WildMatch([Auftraggeber(Kuerzel)],‘* ^ *M3HAEM*’ & ‘*M4DIA*’,‘*M4EN*’,‘*M4GER*’) OR WildMatch([Auftraggeber(Fachrichtung_IntCode)],‘*MUSK*’,‘*ANHA*’)

I search in the table Auftraggeber(Kuerzel) for all entries that do not contain the value M3HAEM, but at the same time correspond to one of the other values. OR from the table Auftraggeber(Fachrichtung_IntCode) corresponds to one of the other values.
However, the exclusion of values beginning with M3HAEM does not work.
WildMatch([Auftraggeber(Fachrichtung_IntCode)],‘*MUSK*’,‘*ANHA*’) works fine. 
However, the expression ((* ^ *M3HAEM*) & (*M4DIA*|*M4EN*|*M4GER*) in itself works.
What am I doing wrong?

Regards
Matthias

[marcus_sommer]

Excluding values combined with further conditions could become quite difficult because it needs often an AND connection, like:

(F1 = 'y' OR F1 = 'z') AND F2 <> 'x'

If I look on your field-names I think the above mentioned categorizing within n hierarchically and/or overlapping dimensions and/or a flagging on them would be more expedient.

[mfrancke]

Hello Marcus,

Thank you very much. Unfortunately, I can't create any new dimensions as I don't have the rights in the system. So I will have to struggle with the search and the exclusion to get a result.
Do you understand why the simple exclusion works in a table and not in connection with the WildMatch function?

Best regards

Matthias

[TauseefKhan]

To exclude entries containing M3HAEM while including other specific patterns, you can use NOT combined with WildMatch() for exclusions:

NOT WildMatch([Auftraggeber(Kuerzel)], '*M3HAEM*') AND
WildMatch([Auftraggeber(Kuerzel)], '*M4DIA*', '*M4EN*', '*M4GER*')

This will exclude entries containing M3HAEM and include those containing M4DIA, M4EN, or M4GER.

Combining with Additional OR Logic:

To include entries based on another field [Auftraggeber(Fachrichtung_IntCode)] with specified patterns, combine the aforementioned logic with an OR condition:

(NOT WildMatch([Auftraggeber(Kuerzel)], '*M3HAEM*') AND
WildMatch([Auftraggeber(Kuerzel)], '*M4DIA*', '*M4EN*', '*M4GER')) OR
WildMatch([Auftraggeber(Fachrichtung_IntCode)], '*MUSK*', '*ANHA*')

Applying in a Filter Pane or Calculated Dimension

Filter Pane Expression: To use this logic in a filter pane:
=If((NOT WildMatch([Auftraggeber(Kuerzel)], '*M3HAEM*') AND WildMatch([Auftraggeber(Kuerzel)], '*M4DIA*', '*M4EN*', '*M4GER*')) OR WildMatch([Auftraggeber(Fachrichtung_IntCode)], '*MUSK*', '*ANHA*'), [Auftraggeber(Kuerzel)])

Calculated Dimension in Chart:
To use this logic in a calculated dimension for a chart:
=If((NOT WildMatch([Auftraggeber(Kuerzel)], '*M3HAEM*') AND WildMatch([Auftraggeber(Kuerzel)], '*M4DIA*', '*M4EN*', '*M4GER*')) OR WildMatch([Auftraggeber(Fachrichtung_IntCode)], '*MUSK*', '*ANHA*'), [Auftraggeber(Kuerzel)])

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