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:
| SKU | Color | Location |
| A | Red | SF |
| B | Blue | NY |
| C | Yellow | LA |
| D | Red | NY |
| E | Blue | SF |
| F | Yellow | DC |
| G | Orange | DC |
| H | | LA |
...and these are the rules...
RULE_TABLE:
| Rule_Priority | Rule_Color | Rule_Location | Rule_Result |
| 1 | | SF | A |
| 2 | Red | NY | B |
| 3 | Blue | LA | C |
| 4 | | DC | D |
| 5 | Yellow | | A |
| 6 | Orange | LA | B |
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:
| SKU | Color | Location | Desired Result | Based on Rule # |
| A | Red | SF | A | 1 |
| B | Blue | NY | | |
| C | Yellow | LA | A | 5 |
| D | Red | NY | B | 2 |
| E | Blue | SF | A | 1 |
| F | Yellow | DC | D | 4 |
| G | Orange | DC | D | 4 |
| 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
