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]

Also there is Mapping_ID for the ID that is referenced in your query. May I know how to get the resultant Mapping_ID for the corresponding ID's that are present in the output of your query ?

[swuehl]

Sorry I don't understand your request.

A bit of explanation how the fields are related, some sample records and your requested output might help.

[vsabbise]

Sorry. I will try to explain here. I am using the below in my filter box expression to display all the ID's.

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

Now there is a Mapping_id which has one to many relationship with ID. I would like to display all the mapping_id instead of ID in my filter box. Is there a way ?

[swuehl]

And you want to show the mapping_id just when there is a relation to ID?

Then maybe a replacement of the ID with Mapping_id might be sufficient. But it's really hard to tell without knowing your exact requirement and expectation.

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

[vsabbise]

I want to show mapping_id that correspond to ID. The problem with the expression you mentioned is that it returns Mapping_id according to the variables and text but I need ID's and then associated Mapping_id instead directly the mapping id's itself.

[swuehl]

Not sure if this makes any difference

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

Again, I am a little confused about your setting. Can you upload a small sample with your expected result?

[vsabbise]

Yes this worked Thank you. One more thing, I was trying to utilize the same expression as a condition to calculate Average Cost.

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

But this doesn't show any result thought syntactically it is correct. Any thoughts ?

I also tried with no luck

count( concat( {<ID = p({<Text = {"$(vText1)*"}>}) * p({<Text = {"$(vText2)*"}>}) >} distinct ID,', ')

[swuehl]

No, that's not valid syntax, because you can't use the Aggr(Only(..)) functions in a set analysis field modifier.

Have you tried with just

Avg( {<ID = p({<Text = {"$(vText1)*"}>}) * p({<Text = {"$(vText2)*"}>}) >} cost)

?

[vsabbise]

Awesome. Thanks. Also what if I were to use Mapping ID instead of ID ? I can replace ID with Mapping ID but that is not the solution. I just need MappingID based on resultant ID's.

Example:

Mapping_ID	ID	text
1a	a2	like
1a	a2	qlik
1a	b3	sense
vText1	qlik
vText2	sense

The above combination of (vText1 and vText2) will not result any ID but will result in '1a' as Mapping ID.

But in my case It shouldn't return any Mapping_id because there are no corresponding ID for the specified logic. So that is why I need Mapping_id corresponding to the ID' but not the vText1, vText2

[swuehl]

Isn't the above set expression doing what you want? I would just let Qlik's associative engine do the rest (linking the Mapping_IDs to the filtered IDs (in your sample, there should be no IDs, hence no associated Mapping_IDs).