Background color rule

[didierodayo]

Hello,

I need some help with this scenario, I have the sample table below ,what I need is to change the background color of the last column (Device Status) based on the rule below.

1- if for any particular school  a device status is Active OR passed and Blank  THEN the device status background should be GREEN()

2- IF  for any particular school  a device status THEN the device status background should be RED()

    I welcome any suggestion. I have attached the sample doc below.

Current
school	Grade	device	device status
USA	2	projector	active
USA	5	laptop	passed
USA	4	cable
USA	3	dock	passed
USA	1	headset	passed
USA	6	phone	passed
CANADA	2	projector	passed
CANADA	5	laptop	passed
CANADA	4	cable	passed
CANADA	3	dock	passed
CANADA	1	headset	passed
CANADA	6	phone	passed

Expected
school	Grade	device	device status
USA	2	projector	active
USA	5	laptop	passed
USA	4	cable
USA	3	dock	passed
USA	1	headset	passed
USA	6	phone	passed
CANADA	2	projector	passed
CANADA	5	laptop	passed
CANADA	4	cable	passed
CANADA	3	dock	passed
CANADA	1	headset	passed
CANADA	6	phone	passed

[Anil_Babu_Samineni]

May be try, I didn't test this

Aggr(If(IsNull([device status]), Green(), Red()),school)

[pathiqvd]

Hi,

    Try This

write Below Exp Inside Background color:-

=if(School='USA',Green(),red())

Regards

[ganeshsvm · Accepted solution]

Hi,

I've used backend scripting to achieve the Solution, Try this,

BGData:
LOAD *,
If(len([device status])<1,'E',Capitalize(Left([device status],1))) as DCode;
LOAD * Inline [
school,Grade,device,device status
USA,2,projector,active
USA,5,laptop,passed
USA,4,cable,
USA,3,dock,passed
USA,1,headset,passed
USA,6,phone,passed
CANADA,2,projector,passed
CANADA,5,laptop,passed
CANADA,4,cable,passed
CANADA,3,dock,passed
CANADA,1,headset,passed
CANADA,6,phone,passed
];

Temp:

LOAD *,
Len(Concat) as CCount;
LOAD *,
if(school=Peek(school) and DCode<>Peek(DCode),DCode&Peek(Concat),DCode) as Concat;
LOAD Distinct school,
DCode Resident BGData Order by school,DCode;

Left Join(BGData)
T1:
LOAD *,
if(len(newKey)>2,1,0) as bgFlag;
LOAD *,
if(school=Peek(school),Peek(newKey),Concat) as newKey
Resident Temp Order By school asc,CCount desc;


Drop fields CCount,newKey,Concat;
DROP Tables Temp;

Then in Front End, use this expression , "if(bgFlag=1,Green(),Red())" in Expression's BackgroundColor option.

Hope this helps.