Counting Multiple Fields, potentially count one field multiple times

[domna]

Hi, 

I'm trying to create a top 5 table based on a count. My only trouble is, this count needs to be based on two fields, one of which may have comma d-limited data. Similar to the example below:

ID	Alert Name	Issue Type
1	Danger	A
2	Warning	See Alert Name
3	Review	T,R,Y
4	Warning	See Alert Name
5	Danger	C
6	Check	N
7	Review	F,R

The output I would expect from here would be:

Warning: 2

R: 2

A: 1

C: 1

N: 1

F: 1

T: 1

Y: 1

I could do this with a bunch of master measures, but I have over 50 unique values. Everything I have tried has come up empty so far.

Thanks

[Taoufiq_Zarra]

if you want to do this in script, One solution :

Data:

load *,subfield(IssueTmp,',') as IssueTmp2;
LOAD *,if([Issue Type]='See Alert Name',[Alert Name],[Issue Type]) as IssueTmp INLINE [
    ID, Alert Name, Issue Type
    1, Danger, A
    2, Warning, See Alert Name
    3, Review, "T,R,Y"
    4, Warning, See Alert Name
    5, Danger, C
    6, Check, N
    7, Review, "F,R"
];

output:


load IssueTmp2 as Error,count(IssueTmp2) as NbrError resident Data group by IssueTmp2;

drop table Data;

output :

[domna]

Thanks for your answer Taoufiq. Is there any way to do this on the front end with a formula? I'll be passing off this work to a team that really doesn't have any experience using the back end, so it's discouraged for me to hand them solutions in the script.