How to create an input rule table

Tue, 18 Oct 2016 23:39:40 GMT

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

topic How to create an input rule table in QlikView

How to create an input rule table