How to use diffrnt operator(like, in, =) from one lookup table file and match column in DB

[jainrishabh]

Hi,

I am stuck with one question and was not able to find the solution for it.

Problem Statement: I have 2 tables in a database

one table contains complete raw data and one lookup table(below in the lookup table(which contains a sample data)), 

Group	Operator1	Field1	Value1	Operator2	Field2	Value2
High	like	Employeeid	1001%,1002%,1003%	!=	employee_name	John
Highest	in	department	Account, Operation
Low	=	address	Los Angeles
Medium	like	Employeeid	%110%,%210%	!=	age	35

I want to create a workflow where we have to use the above lookup table and match the value1 and Value2 using operator1 and Operator2 in the raw data

For eg:

Rule 1: Employeeid like '1001%','1002%','1003%' and employee_name!=John then we have to assign Group='High'

Rule 2: department in Account ,Operation then we have to assign Group='Highest'

Similarly we have to set Rule 3 & 4 

Note:

I have one solution which is kind of static approach where I am using every rule in a Case statement using inDB formula tool. I want this solution to be more dynamic because incase if we change the rule in future then we have to manually update the rule in formula tool as well. 

Can someone please help me with the solution. In case of more detail please post on this thread

Thanks in advance.

Rishabh

[danilang]

Hi @jainrishabh 

Can you post a sample of your raw data?

Thanks

Dan

[jainrishabh]

Hi 

Thank you for the response.

Please find attached the sample data. 

Thanks in advance,

Rishabh Jain

[hellyars]

@jainrishabh I am not getting something.  Have you tried a simple IF statement formula?  Also, what do you mean by "100%,102%,103%" and "%110%,%210%" -- is this a character error or an actual reference to a field in the Sample Raw Data table (if a field, which field)?

***NEVER MIND, I just saw your reference to the IN-DB formula tool***

if [Employeeid]="100%,102%,103%" && [Employee_name]!='John' then 'High'
elseif  [department]='Account, Operation' then 'Highest'
elseif [address]='Los Angeles' then 'Low' 
elseif [Employeeid]='%110%,%210%' and [age] !=35 then 'Med'
else null() 
endif 

 Note: I used a Select tool before the Formula tool to convert Employeeid and Age from a Double to a VString.  You could also convert both in the formula by adding toString() where applicable. 

[jainrishabh]

@hellyars Thanks for the solution,

But currently I am already using the same approach where I am using CASE statement instead of IF statement, but it is a static approach (i.e. in case if Rule 1/Rule 2/Rule3 changes in the future I have to manually update the same in in-DB formula tool).

The Rules are present in another table, I am searching for a solution where I can use that table and can create a dynamic rule.

Thank You,

Rishabh

[apathetichell]

python module and follow this instruction would work. implementing it is another story. There's probably an r solution as well.

https://stackoverflow.com/questions/2983139/assign-operator-to-variable-in-python

[Ben_H]

Hi @jainrishabh,

I think you could do this by creating batch macros for each rule type.

You could run them all against the data and then just identify the ones where all rules have been satisfield.

I've done a crude proof of concept for how that might work for the "like" example - see attached package.

Hopefully that could give you some ideas.

Regards,

Ben