Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
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.