topic Re: Compare 2 WildMatch outputs in QlikView

Compare 2 WildMatch outputs

vsabbise — Fri, 12 Oct 2018 19:41:17 GMT

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

Re: Compare 2 WildMatch outputs

swuehl — Fri, 12 Oct 2018 20:35:38 GMT

Can't really see how one or the other expressions used will create a list of IDs when used in a Qliksense expression.

Can you maybe upload a small sample QVF with some sample records that show your current status?

Re: Compare 2 WildMatch outputs

vsabbise — Fri, 12 Oct 2018 20:44:56 GMT

Hi Stefan,

Thanks for your response. I am using the expression inside a filter pan to display the list of ID's.

For Example: " =if(WildMatch(File_Text,'$(vFileText)*'),id) " This expression when used in a filter pan lists all the id's in my filter pane when my input box(vFileText) string match with the string present in m field(File_text). Thus I can select/un select the ID's i wanted.

Re: Compare 2 WildMatch outputs

swuehl — Fri, 12 Oct 2018 21:15:19 GMT

Ok, so you are using these expressions as calculated dimensions.

How does your data model look like? What's the relation between the IDs and the File texts?

Re: Compare 2 WildMatch outputs

vsabbise — Fri, 12 Oct 2018 21:21:46 GMT

Correct I am using expressions as calculated dimensions. Is there a way to save the output of the expression in a variables and compare and generate a list of common ID's ?

I can literally see what are some of the common ID"s from both the filter panes but wanted to list the common fields.

Regarding Data model, Its a many to many relationship between ID and File Texts.

Re: Compare 2 WildMatch outputs

swuehl — Fri, 12 Oct 2018 22:44:33 GMT

Ok, but what is the relation between the two ID fields? Are they located in one table or in different tables, linked to each other or unlinked?

Re: Compare 2 WildMatch outputs

vsabbise — Mon, 15 Oct 2018 13:42:27 GMT

They are in one table. ID_2 is just a duplicate of ID_1. I created a duplicate column wondering it would make my task easier.

Re: Compare 2 WildMatch outputs

prieper — Mon, 15 Oct 2018 13:52:45 GMT

Think that there is a misunderstanding of the WILDMATCH. Actually this function delivers just one value (or NULL) indication the position of the search-values.

Re: Compare 2 WildMatch outputs

vsabbise — Mon, 15 Oct 2018 14:29:26 GMT

Thanks Peter.

I was using if(WildMatch(File_Text_2,'$(vFileText2)*'),id_2) expression in the filter pane and I was able to see the list of id_2.

I wonder if the output of WildMatch is either a position/Null then how come the " if statement " is able to convert the output to boolean.

Re: Compare 2 WildMatch outputs

prieper — Mon, 15 Oct 2018 15:18:17 GMT

Actually do not think that this formula may work.

Can you post some examples please?

If you wish to compare data, you may rather check the EXISTS-function (or probably work with JOINs).

Peter

Re: Compare 2 WildMatch outputs

vsabbise — Mon, 15 Oct 2018 21:15:16 GMT

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.

Re: Compare 2 WildMatch outputs

swuehl — Mon, 15 Oct 2018 21:33:03 GMT

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,', ')

Re: Compare 2 WildMatch outputs

vsabbise — Tue, 16 Oct 2018 15:54:08 GMT

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

Re: Compare 2 WildMatch outputs

prieper — Wed, 17 Oct 2018 11:41:54 GMT

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

Re: Compare 2 WildMatch outputs

swuehl — Wed, 17 Oct 2018 15:07:48 GMT

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

Re: Compare 2 WildMatch outputs

vsabbise — Thu, 18 Oct 2018 14:38:37 GMT

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() & 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.

Re: Compare 2 WildMatch outputs

swuehl — Thu, 18 Oct 2018 21:07:48 GMT

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?

Re: Compare 2 WildMatch outputs

vsabbise — Fri, 19 Oct 2018 20:08:42 GMT

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.

Re: Compare 2 WildMatch outputs

swuehl — Fri, 19 Oct 2018 20:29:25 GMT

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.

Re: Compare 2 WildMatch outputs

vsabbise — Fri, 19 Oct 2018 20:31:29 GMT

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