Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My Lable logic is not working in Pivot Table.
This is the condition.
=if(Flag='A' and [OrgName]='RX' and [Org Customer]='Yes',
'Score',
if(Flag='B' and [OrgName]='RX' and [Org Customer]='Yes',
'Score',
if(Flag='C' and [OrgName]='RX' and [Org Customer]='Yes',
'Score',
if(Flag='D' and [OrgName]='RX' and [Org Customer]='Yes',
'Rank'
))))
Expected Output:
Present output:
My Source Data
Data:
LOAD * INLINE [
Org Customer,CustName,Flag,OrgName,Ascore,Bscore,Cscore,Dscore
Yes,Cust1,B,A1,Good,Good,Good,1st
Yes,Cust1,B,A2,Average,Average,Average,2nd
Yes,Cust2,B,A1,Outstanding,Outstanding,Outstanding,1st
Yes,Cust2,B,A2,Average,Average,Average,2nd
Yes,Cust1,B,RX,Average,Average,Average,1st
Yes,Cust1,A,A1,Good,Good,Good,2nd
Yes,Cust1,A,A2,Average,Average,Average,Not Ranked
Yes,Cust1,A,A3,Good,Good,Good,1st
Yes,Cust2,A,A1,Good,Good,Good,Not Ranked
Yes,Cust2,A,A2,Average,Average,Average,1st
Yes,Cust2,A,A3,Outstanding,Outstanding,Outstanding,Not Ranked
Yes,Cust1,A,RX,Average,Average,Average,1st
Yes,Cust1,A,RX,Average,Average,Average,Not Ranked
Yes,Cust2,C,,Good,Good,Good,1st
Yes,Cust1,C,RX,Average,Average,Average,1st
Yes,Cust1,D,A14,Good,Good,Good,1st
Yes,Cust1,D,A15,Good,Good,Good,2nd
Yes,Cust1,D,A1,Average,Average,Average,1st
Yes,Cust1,D,A2,Outstanding,Outstanding,Outstanding,2nd
Yes,Cust1,D,A3,Average,Average,Average,1st
Yes,Cust1,D,RX,Average,Outstanding,Outstanding,2nd
];
hello
I think there's a problem with the logic of the test
you are grouping by Flag value
but for ONE value of Flag, you can have multiple values of OrgName
(example : select Cust1, Flag=A -> 4 Orgname : A2 , RX, A1, A3)
what is the expected output in this case ?
select Cust1, Flag=A -> 4 Orgname : A2 , RX, A1, A3 : Expected out is Score. I am taking only RX values
in the expression value, yes
but not in the label value
=AGGR(if(Wildmatch(Flag,'A','B','C'), 'Score',if(Wildmatch(Flag,'C'),'Rank')),Flag)
Please use this data:
Data:
LOAD * INLINE [
Org Customer,CustName,Flag,OrgName,Ascore,Bscore,Cscore,Dscore
Yes,Cust1,B,A1,Good,Good,Good,1st
Yes,Cust1,B,A2,Average,Average,Average,2nd
Yes,Cust2,B,A1,Outstanding,Outstanding,Outstanding,1st
Yes,Cust2,B,A2,Average,Average,Average,2nd
Yes,Cust1,B,RX,Average,Average,Average,1st
Yes,Cust1,A,A1,Good,Good,Good,2nd
Yes,Cust1,A,A2,Average,Average,Average,Not Ranked
Yes,Cust1,A,A3,Good,Good,Good,1st
Yes,Cust2,A,A1,Good,Good,Good,Not Ranked
Yes,Cust2,D,RX,Average,Average,Average,1st
Yes,Cust2,A,A3,Outstanding,Outstanding,Outstanding,Not Ranked
Yes,Cust1,A,RX,Average,Average,Average,1st
Yes,Cust1,A,RX,Average,Average,Average,Not Ranked
Yes,Cust2,C,,Good,Good,Good,1st
Yes,Cust1,C,RX,Average,Average,Average,1st
Yes,Cust1,D,A14,Good,Good,Good,1st
Yes,Cust1,D,A15,Good,Good,Good,2nd
Yes,Cust1,D,A1,Average,Average,Average,1st
Yes,Cust1,D,A2,Outstanding,Outstanding,Outstanding,2nd
Yes,Cust1,D,A3,Average,Average,Average,1st
Yes,Cust1,D,RX,Average,Outstanding,Outstanding,2nd
];
A typo, the second condition should be for D
Not in front of my PC so cannot check your app
=AGGR(if(Wildmatch(Flag,'A','B','C'), 'Score',if(Wildmatch(Flag,'D'),'Rank')),Flag)
Hi Vineeth, Thank you for the reply.
But it is not working.
Hi,
Try like this.