Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
didierodayo
Partner - Creator III
Partner - Creator III

Background color rule

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
schoolGradedevicedevice status
USA2projectoractive
USA5laptoppassed
USA4cable
USA3dockpassed
USA1headsetpassed
USA6phonepassed
CANADA2projectorpassed
CANADA5laptoppassed
CANADA4cablepassed
CANADA3dockpassed
CANADA1headsetpassed
CANADA6phonepassed

   

Expected
schoolGradedevicedevice status
USA2projectoractive
USA5laptoppassed
USA4cable
USA3dockpassed
USA1headsetpassed
USA6phonepassed
CANADA2projectorpassed
CANADA5laptoppassed
CANADA4cablepassed
CANADA3dockpassed
CANADA1headsetpassed
CANADA6phonepassed
1 Solution

Accepted Solutions
ganeshsvm
Creator II
Creator II

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.

bgColor.JPG

Hope this helps.

View solution in original post

3 Replies
Anil_Babu_Samineni

May be try, I didn't test this

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
pathiqvd
Creator III
Creator III

Hi,

    Try This

Test.JPG

write Below Exp Inside Background color:-

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

Regards

ganeshsvm
Creator II
Creator II

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.

bgColor.JPG

Hope this helps.