topic Re: count when wildmatched in QlikView

count when wildmatched

Wed, 14 Sep 2016 03:46:31 GMT

Hi All,

I have a table with some names on it. I want a chart to display the count of occurences if the name is mentioned anywhere in another column.

Chart dimention will be the list of names. the expression needa to count the dimention value if it mentioned anywhere. Both are excel sheets.

thanks in advance.

Chanu

Re: count when wildmatched

sunny_talwar — Wed, 14 Sep 2016 15:40:26 GMT

Would you be able to share a sample with expected output?

Re: count when wildmatched

Thu, 15 Sep 2016 05:15:21 GMT

Hello Sunny,

Its like this.

An example would be

Table 1 Table 2

LOAD*INLINE LOAD*INLINE

name1 an issue reported for name1@company.com on ....

name2 name2@domain.com tried to connect...

name3 this issue reported behalf of name2....

When a chart created I want my dimensions to be Table 1, and expression would be the count of table 2 entries that has dimension value.

As in this case the chart would display Counts= 1 for name1, 2 for name2 and 0 for name3.

I have already created a counter using a variable that provides number of occurrences when each of table1 value is clicked from a listbox. I did it using wildcards (ex:*name1*). but what I really need is a chart where I can see altogether.

Does it makes sense?

Cheers

Chanu

Re: count when wildmatched

sunny_talwar — Thu, 15 Sep 2016 10:05:38 GMT

May be like this?

=Sum(Aggr(If(WildMatch(Desc, '*' & Name & '*'), 1, 0), Desc, Name))

Re: count when wildmatched

Thu, 15 Sep 2016 10:21:32 GMT

Sunny Thank you. I think my example was bit misleading. Names are not always start by the prefix 'name'. They could be any arbitrary value,

Table 1 Table 2

LOAD*INLINE LOAD*INLINE

John an issue reported for john@company.com on ....

Dave dave@company.com tried to connect...

Anna this issue reported behalf of dave....

Is there anyway to wildmatch the current dimension that's been calculated?

Thanks,

Chanu

Re: count when wildmatched

sunny_talwar — Thu, 15 Sep 2016 18:36:51 GMT

Still seems to be working, what is the issue?

Re: count when wildmatched

Fri, 16 Sep 2016 06:40:10 GMT

Hello Sunny,

It was my fault. your suggestion is absolutely correct and was exactly what I wanted. Many Thanks. My actions however doesn't allow me to mark your reply as the answer. Can someone do that for me pl?

PS:

Even the formula is what I expected it exhaust the memory after 500 lines of data. Is there anyway I can use this on the loadscript. This is what i'm expecting it to do

If a name from table 1 wildmatched to 'desc' column on table 2, save the name that matched on to a new column on table 2.

Thanks again.

Chanu

Re: count when wildmatched

sunny_talwar — Fri, 16 Sep 2016 15:58:12 GMT

Script based solution:

Table1:

LOAD * Inline [

Name

John

Dave

Anna

];

Table2:

LOAD * Inline [

Desc

an issue reported for john@company.com on ....

dave@company.com tried to connect...

this issue reported behalf of dave....

];

Join(Table1)

LOAD *

Resident Table2;

FinalTable1:

LOAD Name,

Sum(If(WildMatch(Desc, '*' & Name & '*'), 1, 0)) as Count

Resident Table1

Group By Name;

DROP Table Table1;