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.
