Applying dynamic rule data managed in Excel to dataset

[nsarayar]

Hi

Is there an easy way to apply a set of rules managed in Excel to a dataset so I don't have to write endless  if then else statements?

So using the below table, if any of the values match the input columns, the value in the output column is written.

Thanks very much!

Input	Input	Input	Input	Output	Output
Product_Type	ext_sdi_status	SDI_Type	Settled_User	Settlement STP/Manual	Source User
InterestBearing	Default	PREFERRED	Null	STP	N/A
InterestBearing	Xfer Assigned	PREFERRED	Not Null	Medium	[Settled_User]
InterestBearing	Xfer Assigned	"NON-PREFERRED"		Medium	[Setled_User]
InterestBearing	Manual	MANUAL		High	Setled_User]
CASH	Xfer Assigned	PREFERRED		Medium	Setled_User]
and so on for 60 odd rules

[atcodedog05]

Hi @nsarayar 

Can you please provide details on the logic with an example.

[danilang · Accepted solution]

Hi @nsarayar 

Formulas are data, too!  You can use Alteryx to dynamically build the If-then statements from the Excel formula source

The Top branch is your data input without the two output fields.  The next Formula tool adds the two output field and sets them to null.  The bottom branch is where the If-Then statements are built.  There's one sub branch for each of the output fields.  The Formula tool builds the clause that corresponds to each row 

"[Product_Type]='"+[Product_type] +"' and [ext_sdi_status]='"+[ext_sdi_status] + "' and [SDI_TYPE]='"+[SDI_Type]+ IIF(trim([Settled_User])="", "'", "' and [Settled_User]='"+[Settled_User]+"'")+" Then '"+[Settlement STP/ManualOutput]+"'"  

It also adds in extra fields required by the dynamic replace tool.  Each of the summarize tools concats all the clauses with  the proper Start, Separator and End text to make a valid If-Then statement 

The two formulas are then unioned together.  The next Dynamic Replace tool is unique in Alteryx in that it's the only tool that can evaluate input data on the R anchor as formulas and apply them to the data coming in the D anchor.  When run, you get the results in the two output columns.  

The solution is dynamic.  When you modify rows in the formula input, the resulting formulas are applied to your input data

Note:  The values in your input formula table had trailing spaces in both the source column names and some of the data values.  I cleaned these up, so you'll need to be careful when applying this to your real world data.

Dan

[nsarayar]

Amazing!! thank you @danilang 

[nsarayar]

@danilang 

sorry quick question, how do you account for rules such as NOT and IsNull / Is not Null and NOT Value 1 or Value 2?

and where I have "Settled_User", I actually want to take the value in the "Settled_User" column of the data. So if in the data it says John or Jill, for example, I want it to appear in the output and not the actual text of "[Settled_User]