count when wildmatched

[#1]

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar · Accepted solution]

May be like this?

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

[#5]

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

[sunny_talwar]

Still seems to be working, what is the issue?

[#7]

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

[sunny_talwar]

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;