topic Re: How to replace columns based on dropdown tokens? in Dashboards & Visualizations

How to replace columns based on dropdown tokens?

splunklakshman — Tue, 29 Sep 2020 13:16:58 GMT

Dear All,

When i select Tractor, i need to get the two columns in below table like VEHICLE_NAME,UNITS
When i select ZEEP, i need to get the two columns in below table like VEHICLE_NAME,UNITS1

Please find the code below. Kindly suggest. Thanks in Advance!

SAMPLE_TEST

<input type="dropdown" token="VEHICLE_NAME" searchWhenChanged="true">
  <label>Select Vehicle</label>
  <choice value="TRACTOR">TRACTOR</choice>
  <choice value="ZEEP">ZEEP</choice>
  <fieldForLabel>VEHICLE_NAME</fieldForLabel>
  <fieldForValue>VEHICLE_NAME</fieldForValue>
  <search>
    <query>index=idx_color | table VEHICLE_NAME | dedup VEHICLE_NAME</query>
    <earliest>0</earliest>
    <latest></latest>
  </search>
  <default>TRACTOR</default>
  <initialValue>TRACTOR</initialValue>
</input>


<panel>
  <table>
    <search>
      <query>index=idx_color VEHICLE_NAME=$VEHICLE_NAME$ | eval UNITS1=coalesce(UNITS1,UNITS)| table VEHICLE_NAME , UNITS,UNITS1 |</query>
      <sampleRatio>1</sampleRatio>
    </search>
    <option name="count">20</option>
    <option name="dataOverlayMode">none</option>
    <option name="drilldown">cell</option>
    <option name="percentagesRow">false</option>
    <option name="rowNumbers">false</option>
    <option name="totalsRow">false</option>
    <option name="wrap">true</option>
  </table>
</panel>

Re: How to replace columns based on dropdown tokens?

somesoni2 — Tue, 21 Mar 2017 16:34:43 GMT

Will there be always only 2 options in your dropdown, JEEP and TRACTOR?

Re: How to replace columns based on dropdown tokens?

splunklakshman — Tue, 21 Mar 2017 16:37:50 GMT

Jeep and Tractor only in this scenario . Pls help.

Re: How to replace columns based on dropdown tokens?

somesoni2 — Tue, 21 Mar 2017 16:41:26 GMT

Change your panel search like this
Updated

 <query>index=idx_color VEHICLE_NAME=$VEHICLE_NAME$ | eval UNITS1=coalesce(UNITS1,UNITS)| table VEHICLE_NAME , [|gentimes start=-1 | eval query=if("$VEHICLE_NAME$"="TRACTOR","UNITS","UNITS1") | table query | format "" "" "" "" "" ""]</query>

Re: How to replace columns based on dropdown tokens?

splunklakshman — Tue, 21 Mar 2017 16:45:51 GMT

Error in 'eval' command: The arguments to the 'if' function are invalid.

Re: How to replace columns based on dropdown tokens?

DalJeanis — Tue, 21 Mar 2017 17:29:44 GMT

should that be

| eval query=if("$VEHICLE_NAME$"="TRACTOR","UNITS","UNITS1")

| eval query=if('$VEHICLE_NAME$'="TRACTOR","UNITS","UNITS1")

I can never remember when single-quotes are required... but this is itching like it might be the case...

Re: How to replace columns based on dropdown tokens?

niketn — Tue, 29 Sep 2020 13:17:00 GMT

If you want to use field name as UNITS when VEHICLE_NAME is TRACTOR and fieldname as UNITS1 when the VEHICLE_NAME is ZEEP and both UNITS and UNITS1 are existing fields available in your indexed data, you can try the following (Dropdown's change event handler is coded using eval to set token for dynamic field name😞

 <input type="dropdown" token="VEHICLE_NAME" searchWhenChanged="true">
   <label>Select Vehicle</label>
   <choice value="TRACTOR">TRACTOR</choice>
   <choice value="ZEEP">ZEEP</choice>
   <fieldForLabel>VEHICLE_NAME</fieldForLabel>
   <fieldForValue>VEHICLE_NAME</fieldForValue>
   <search>
     <query>index=idx_color | table VEHICLE_NAME | dedup VEHICLE_NAME</query>
     <earliest>0</earliest>
     <latest></latest>
   </search>
   <default>TRACTOR</default>
   <initialValue>TRACTOR</initialValue>
   <change>
       <eval token="tok_fieldName">case($value$==&quot;TRACTOR&quot;,&quot;UNITS&quot;,1==1,&quot;UNITS1&quot;)</eval>
   </change>
 </input>

 <panel>
   <table>
     <search>
       <query>index=idx_color VEHICLE_NAME=$VEHICLE_NAME$ | table VEHICLE_NAME , $tok_fieldName$ |</query>
       <sampleRatio>1</sampleRatio>
     </search>

Re: How to replace columns based on dropdown tokens?

splunklakshman — Tue, 21 Mar 2017 17:47:35 GMT

Thanks for your help Niketnilay, i have applied the changes, the logic is working fine but when i select Jeep, the UNITS1 value 10 is not coming. Can you please help.

Re: How to replace columns based on dropdown tokens?

splunklakshman — Tue, 21 Mar 2017 17:49:26 GMT

Sorry the UNITS Value 20 is not coming. Can you please help.

Re: How to replace columns based on dropdown tokens?

splunklakshman — Tue, 21 Mar 2017 18:01:53 GMT

Typo error for UNITS1 , value 20 is not displaying in the row. kindly help.

WHILE SELECT ZEEP FROM DROPDOWN, THE RESULT IS AS BELOW.

VEHICLE_NAME UNITS1
ZEEP

Re: How to replace columns based on dropdown tokens?

somesoni2 — Tue, 21 Mar 2017 18:02:48 GMT

Thanks @DalJeanis, it was a typo where I was missing equal sign for comparing.

We need single quotes for field names, here the token $VEHICLE_NAME$ gives value (TRACTOR/JEEP), so double quotes is appropriate.

Re: How to replace columns based on dropdown tokens?

DalJeanis — Tue, 21 Mar 2017 20:20:27 GMT

...pulls out stubby pencil and writes down "token names are not field names..."

Re: How to replace columns based on dropdown tokens?

splunklakshman — Wed, 22 Mar 2017 01:37:38 GMT

Dear Somesoni and DalJeanis,

Thanks for the solution, it works as expected.

Re: How to replace columns based on dropdown tokens?

niketn — Wed, 22 Mar 2017 02:28:19 GMT

@splunklakshman... Please accept @somesoni2 ' s answer and also upvote @DalJeanis comment, since that is the complete solution for your issue.

Re: How to replace columns based on dropdown tokens?

niketn — Wed, 22 Mar 2017 02:41:43 GMT

@splunklakshman I am not sure what is not working the logic will use field name as UNITS when TRACTOR is selected and UNITS1 when anything else is selected (i.e. for others case is 1==1). Following is simplified version of the same as eval expression to set token will allow double quotes to be used within Simple XML.

<eval token="tok_fieldName">case($value$=="TRACTOR","UNITS",1==1,"UNITS1")</eval>

May be the search query will work if I use original one with coalesce command that you had used. However, I fail to see why you would use coalesce, if you are bothered only for specific column for specific VEHICLE_NAME. Following is the query I think you need:

<query>index=idx_color VEHICLE_NAME=$VEHICLE_NAME$ | eval UNITS1=coalesce(UNITS1,UNITS) | table VEHICLE_NAME , $tok_fieldName$ |</query>

Re: How to replace columns based on dropdown tokens?

niketn — Wed, 22 Mar 2017 02:42:40 GMT

I have added comment to my answer. I did not think you required coalesce command since field names UNITS and UNITS1 were related with TRACTOR and ZEEP respectively.

Re: How to replace columns based on dropdown tokens?

DalJeanis — Wed, 22 Mar 2017 03:37:27 GMT

converted somesoni's answer/comment to an answer so that op CAN accept it. 😉