0 Replies Latest reply: Oct 18, 2016 7:39 PM by helen gonzalez RSS

    How to create an input rule table

    helen gonzalez

      Hello,

       

      I am trying to set up a dashboard that will read an Excel input "rule table", such that a user can modify the rule table and the dashboard will apply the new rules when the script is refreshed.  The script should compare the rule table, which contains values in various fields, to the same fields in the larger dataset (that is read using ODBC). Based on matching data between the rule table and the data set, the script will assign a new value in a new field, as defined in the rule table.

       

      For example, if this is the data:

       

      DATA:

       

      SKUColorLocation
      ARedSF
      BBlueNY
      CYellowLA
      DRedNY
      EBlueSF
      FYellowDC
      GOrangeDC
      H LA

       

      ...and these are the rules...

       

      RULE_TABLE:

             

      Rule_PriorityRule_ColorRule_LocationRule_Result
      1 SFA
      2RedNYB
      3BlueLAC
      4 DCD
      5Yellow A
      6OrangeLAB

       

      Note that the null values in "Rule_Color" and "Rule_Location" should be interpreted as any value is okay, including NULL.

       

      The script should return these results:

       

      SKUColorLocationDesired ResultBased on Rule #
      ARedSFA1
      BBlueNY
      CYellowLAA5
      DRedNYB2
      EBlueSFA1
      FYellowDCD4
      GOrangeDCD4
      H LA

       

      I have tried various combinations of NULL functions and value-handling variables such as "OtherSymbol" but I can't seem to get the right combination.  I was hoping I could concatenate the fields being analysed, and use * or some other symbol to have have the script interpret those as any value, but this didn't work.

       

      Any help or ideas are welcome.  I have attached a QVW and related data set here.

       

      Thank you!

      Helen G