Dynamic Formula by looping through Excel

[CodingMan]

Hi Folks in Alteryx Community,

I was trying to apply multiple conditions to a data set to filter data. I stored my conditions in another Excel sheet. I want Alteryx to loop through that condition file, change formula based on the values in that file and then filter my data.

Here is my data, it has client ID, carrier ID and dollar amount of orders. But the condition is really complicated as shown in the picture below

The third column indicates if I want to look up the multiple of Column Increment. The fourth column indicates if I want to look up value that is less than increment.

For example, if carrier is A and MultipleOrNot = 1 and LessThanIncrement is 0. In my data, I want all carrier A records that with a dollar amount of 0.05/-0.05 or multiples of 0.05/-0.05.

Or, if carrier is A and MultipleOrNot = 1 and LessThanIncremrnt is 1. In my data, I want all carrier A records that with a dollar amount of 0.05/-0.05 or multiples of 0.05/-0.05 or is between -0.05 and 0.05. Then move to the next condition.

So basically the formula will change based on the value in MultipleOrNot and LessThanIncrement. Something like 

if MultipleOrNot = 1 AND LessThanIncrement =1, formula is if MOD(value, increment) =0 or value is between -increment and increment.

Then it will go to my data, say, if carrier = A, increment = 0.05. Then filter my data.

I actually asked the question before, join two tables will work perfectly. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Formula/m-p/313866

But now, the carrier is not unique in the rule table. Therefore, I can't join data table with rule table. I think loop through the rule table will be the only way.

What I want is a formula that can change based on the value in the rule table and then apply it to my data set to filter it.

Any thoughts?

[jdunkerley79]

The join doesn't need to be 1 to 1. A 1 to many can work. You can add a unique ID to then produce a unique output set.

I think good to understand more of the process you want:

if MultipleOrNot = 1 AND LessThanIncrement =1, formula is if MOD(value, increment) =0 or value is between -increment and increment.

- MOD(value, incremenent) = 0 means a multiple of increment

- The value between increment and -increment means that the only valid values are 0, increment and -increment?

Is that the logic you mean?

Based on how I read your statements I think:

First do the 1:many join

Then handle the MultipleOrNot conditions

Next handle the Increments conditions

Final Go back to a unique input set

Hopefully this gives you enough to break down the problem

[CodingMan]

I don't understand the 1 to many join. You can see carrier F has 3 conditions in the rule table. How can three rows join with one row from data table?

if MultipleOrNot = 1 AND LessThanIncrement =1, formula is if MOD(value, increment) =0 or value is between -increment and increment.

The value between increment and -increment means that any value between -increment and increment.

I am trying to create a batch Macro that will apply conditions one by one and union all results together. I will keep adding rules in the future so I think I should create a Macro because I can use it no matter how many conditions I have or what the condition is.

[jdunkerley79]

The 1:many is just meaning that all matching carrier and clients join together. Looking closer at your data its actually many to many. Many filters and many orders joined together,

Ah ok I misread the concept thought was an AND operator.

Still don't think you need a batch macro. I think this should do pretty much what you asked.

This will evaluate the LessThan and Multiple conditions and then union the results before creating a unique set of rules and orders

[CodingMan]

The join works perfectly. And I just tested that you can join two tables without adding those IDs.

However, I am testing different ways to do the same thing. I will really appreciate it if you can give me some advice on how to do this with a Macro. The formula will change based on the value in the table. The formula I have right now is: (I forgot to add carrierID as a condition)

if [MultipleOrNot] = 1 Then

	if [LessThanIncrement] = 1 then

	(IsInteger([Order $]/[Increment]) OR ABS([Order $])<= ABS([Increment]) )AND [Order $] >=[LowerLimit] AND [Order $] <=[UpperLimit]
	
	Else
	IsInteger([Order $]/[Increment]) AND [Order $] >=[LowerLimit] AND [Order $] <[UpperLimit]

	Endif

Else
	
	if [LessThanIncrement] = 1 then
	ABS([Order $]) <= ABS([Increment]) AND [Order $] >=[LowerLimit] AND [Order $] <=[UpperLimit]
	
	Else
	ABS([Order $]) = ABS([Increment])

	Endif

Endif	

I want Alteryx to read the values in each row. update the formula, filter my data and union results. Each line in the rule table is a condition or formula I want to apply. The code above is just like a template. And I hope the Macro can update values in that template and then apply.

Thanks for your help again. Really appreciate that.

[jdunkerley79 · Accepted solution]

The attached package should have what you want to get started doing this macro approach.

A macro takes the spreadsheet and converts it into a set of filter expressions (basically reproducing the filter expression you had).

These are then passed to a batch macro to apply a dynamic filter to data set one row at a time

If a row passes more than one filter it will be returned multiple times. 

Hopefully enough for you to dig through and see what is what. Am happy to answer further questions on it.

[CodingMan]

I do have two more general questions:

1. For the Macro Input tool, did you type in the value '60 Rows & 3 Columns' or Alteryx generate it?

2. I still don't know how the Control Parameter tool and action tool work together. In my mind, I should be able to select what values or fields I want to update.

       And in the configuration of Control Parameter, I can't browse the input. I know it's connected by the inverse question mark. But I got an error message said Control Parameter should be mapped out to a field.

Thank you for your help again. I guess I will learn the Macro you gave to me and hopefully I can understand every piece of it.

[CodingMan]

And for these two steps. What's the first step for? And what's the syntax in the second tool? (Those quotes and plus signs)  

       

 

[jdunkerley79]

For 1. I ran the other workflow to get the data into the browse window. You can then copy all data and paste it into a workflow as a text input. Right click on the text input and you can convert to macro input.

For 2. A control parameter is fed one value at a time. You can then either take this in as a question value to a tool or use an action to manipulate a tools configuration as I did in the underlying macro.

Hopefully you can pull apart but feel free to ask on anything you don’t understand

[CodingMan]

Hello,

Can you tell me why you put 1=1 in the filter tool within the Macro