Dynamically fetch the conditions to a IF ELSE condition statement

[Debapriya]

I have an input data with the columns: (Data1) (file name: data set)

Serial No.

age

work class

Salary PA

education

marital-status

occupation

gender

hours-per-week

native-country

I have separate data sheet for the conditions to be applied on the above data: (Data2) (filename : Rules)

Rule Serial No

age lower limit

age upper limit

Salary Lower Limit

Salary Upper Limit

Gender

Class

Problem:

The Age, Salary PA & Gender (Data1) are the focus columns on which the data need be segregated and the Class(data2) would be appended to the Data1 as a new column.

The Conditions data (Data2) needs to be fed dynamically to the IF ELSE statements, since the conditions change periodically. (can't be hardcoded)

Conditions need to be compared with a FOR Loop, and the CLASS value to be set/defined for each row of the Data1.

Example:

The final data should give an output as: 

age

work class

Salary PA

education

marital-status

occupation

gender

hours-per-week

native-country

Class (from Data2)

Below i have written a code in a made-up language. treat this as  a logic:

• For Each [Serial No]
  • For Each [Rule Serial No]
    • If [Data1.Gender] = [Data2.Gender] and [Age] <=  [age upper limit] and [Age] >=  [age lower limit]
      • and [Salary PA] <= [Salary Lower Limit] and [Salary PA] >= [Salary Upper Limit]  
      • then [Class]
    • Else
      • "Error"
    • Endif

Can this be automated in Alteryx? refer attachments for Data.

[Ladarthure · Accepted solution]

Hi @Debapriya,

if you want to do this in Alteryx, you will not need an iterative macro to do so. In your case, you can use a 2 steps method to do so :

1. Append your rules to your main dataset with the Append Tool
   1. it will allow you to multiply each line by all the rules you have
2. Use a custom filter in which you do something like the formula bellow:
   1. [Data1.Gender] = [Data2.Gender] and [Age] <=  [age upper limit] and [Age] >=  [age lower limit] and [Salary PA] <= [Salary Lower Limit] and [Salary PA] >= [Salary Upper Limit] 
   2. this will filter to only take the data in your ranges.

Tell me if it's not clear,

Arthur

[Debapriya · Accepted solution]

Thank you so much.