How to color duplicate cell values dashboard table...

genesiusj · ‎02-23-2022

Hello,

Need to color cells in a dashboard table based on duplicate cell values (2 or more) within the same row.

Here is the formatting code for the attached example.

<format type="color">
   <colorPalette type="sharedList"></colorPalette>
   <scale type="sharedCategory"></scale>
</format>

Thanks and God bless,
Genesius

ITWhisperer · ‎03-02-2022

You could try adding a multivalue to the cells which have duplicates in the row and using that to colour the cells (using CSS to hide the extra value)

| untable Field1 FieldName value
| eventstats count by Field1 value
| eval value=if(count>1,mvappend(value,"DUPLICATE"),value)
| xyseries Field1 FieldName value

genesiusj · ‎03-03-2022

@ITWhisperer

Thank you. I should have mentioned that my code uses | transpose. Here is the code.

| dbxquery connection="DB2" query="select distinct IDN, UIB, DTE_UIB, NME_LAST_CLMT, NME_FIRST_CLMT, NME_MIDDLE_CLMT, EMAIL, ADD_STREET_MAIL, ADD_CITY_MAIL, ADD_STATE_MAIL, ADD_ZIP_MAIL, PHONE, ADD_STREET_RES, ADD_CITY_RES, ADD_STATE_RES, ADD_ZIP_RES from CLMT where ((TO_CHAR(PHONE) like '%9087777777%') or (ADD_STREET_MAIL like '%1 MAIN STREET%')) " 
| eval zipCodeM=if(len(ADD_ZIP_MAIL)=9 or len(ADD_ZIP_MAIL)=5,substr(ADD_ZIP_MAIL,1,5),substr(printf("%09d",ADD_ZIP_RES),1,5)) 
| eval zipCodeR=if(len(ADD_ZIP_RES)=9 or len(ADD_ZIP_RES)=5,substr(ADD_ZIP_RES,1,5),substr(printf("%09d",ADD_ZIP_RES),1,5)) 
| table IDN, UIB, DTE_UIB, NME_LAST_CLMT, NME_FIRST_CLMT, NME_MIDDLE_CLMT, EMAIL, ADD_STREET_MAIL, ADD_CITY_MAIL, ADD_STATE_MAIL, ADD_ZIP_MAIL, zipCodeM, PHONE, ADD_STREET_RES, ADD_CITY_RES, ADD_STATE_RES, ADD_ZIP_RES, zipCodeR 
| eval Name=if(isnotnull(NME_MIDDLE_CLMT),trim(NME_LAST_CLMT).", ".trim(NME_FIRST_CLMT)." ".trim(NME_MIDDLE_CLMT),trim(NME_LAST_CLMT).", ".trim(NME_FIRST_CLMT)) 
| eval IDN = printf("%07d",IDN)
| eval CCODE=IDN.",".UIB.",".DTE_UIB 
| `convert_to_String(CCODE)` 
| eval cCode=String." ".Name 
| rex field=cCode mode=sed "s/ /\n/"
| fields - Name, ADD_ZIP_MAIL, ADD_ZIP_RES, String, CCODE 
| transpose 0 column_name="Field" header_field=cCode

I am not familiar with the untable and xyseries commands. As I am using transpose, would they still function properly?
Here is the XML.

  <row>
    <panel>
      <html>
        <style>
          .table th {
            background-color: black !important;
            color: white !important;
          }
        </style>
      </html>
      <table>
        <search>
          <query> 
 :
 :   <SPL IS IN THE ABOVE CODE SAMPLE BOX>
 :
          <earliest>1583038800</earliest>
          <latest>1645623637</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
        <format type="color" field="Field">
          <colorPalette type="list">[#000000]</colorPalette>
        </format>
      </table>
    </panel>
  </row>

Thanks and God bless,
Genesius

genesiusj · ‎03-01-2022

I found a partial answer.

The column named Field - Note the order of the format tags matters.

I hope one of the fezzes can figure out the other issue: cells with single values should not be colored.

Thanks and God bless,
Genesius

How to color duplicate cell values dashboard table?

other

Announcing Scheduled Export GA for Dashboard Studio

Extending Observability Content to Splunk Cloud

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!