Compare 2 WildMatch outputs

[vsabbise]

I am looking for a solution to compare 2 WildMatch outputs in Qlik Sense Expression. In fact a way to code nested WildMatch .

I have my first list of id's generated by first Wildmatch expression And Second list of Id's generated by Second Wildmatch expression

Now I want common Id's from either of the lists

Ex: I want something like below:

=if (   wildmatch(

     if(WildMatch(File_Text_2,'$(vFileText2)*'),id_2),     if(WildMatch(File_Text,'$(vFileText)*'),id_1)

                       ),id_1)

Explanation:

if(WildMatch(File_Text_2,'$(vFileText2)*'),id_2)  : This generates list of id_2

if(WildMatch(File_Text,'$(vFileText)*'),id_1)            : This generates list of id

Now I want to list the common id's that are present in both the lists id_2 & Id_1

[vsabbise]

Thanks Peter. Could you let me know if I can use Exists in Qlik Sense Expressions ? to create calculated dimensions ? in filter pane ?

I tried to use this but it says incomplete visualization:

=if(Exists(File_Text_2,'$(vFileText2)*'),id_2)

File_Text_2 is the field which contains 1 word strings

vFilteTxt2 is an input box variable where i enter a string value to see if it is present in the field or not

Finally I wanted to display all id's correspond to a match.

[swuehl · Accepted solution]

So basically you have one column with text and one with IDs?

In a filter pane, try something like

=Aggr( Only({<ID = p({<Text = {"$(vText1)*"}>}) * p({<Text = {"$(vText2)*"}>}) >} ID), ID)

Or in a chart expression to get the concatenated common IDs:

=Concat({<ID = p({<Text = {"$(vText1)*"}>}) * p({<Text = {"$(vText2)*"}>}) >} DISTINCT ID,', ')

[vsabbise]

Awesome first option worked for me. Could you explain what have you done here.

[prieper]

Am not familiar with QSense, but think that EXISTS-function works only in script, not in expressions.

[swuehl]

It's a translation of your requirements to set analysis, the two p() functions are each returning your list of IDs that are related to a search of your variables in Text column. The intersection operator * is then returning the intersection (the IDs that are in common in both lists).

If you are not familiar with set analysis, that's an advanced topic, but very powerful.

Start with

A Primer on Set Analysis

[vsabbise]

Thanks Stefan. I am aware of Set analysis and tried something below to check the count of items in the list but it never worked for me. May I know what does p() do ? Looks like P() can help with nested set analysis ? P() &amp; E() and where do you use them?

Count( {<File_Text={'$(#vFileText)*'}>*<File_Text_2={'$(#vFileText2)*'}>}id)

The above expression when coded as an expression for KPI chart it returns 0 records.

[swuehl]

If field File_Text_2 is just a duplicate of field File_Text and you are filtering different sets (that's just an assumption, because you don't talk much about the filter context or show samples), then it's no surprise that the intersection of the record sets is an empty set, isn't it?

[vsabbise]

Right it is resulting in empty dataset. How is this different from what you have come up with since even you were also using intersection operator * along with returned lists.

[swuehl]

I've calculated the intersection of the related ID field values (to define the element set), while you were calculating the intersection on the record set level.

[vsabbise]

Ah I see. Thanks for the help. I marked the correct answer.