Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Formula by looping through Excel

CodingMan
7 - Meteor

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.

Capture.PNG

 

 

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

Capture.PNG

 

 

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.

Capture.PNG

 

 

 

 

 

 

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?

 

 

10 REPLIES 10
jdunkerley79
ACE Emeritus
ACE Emeritus

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:

2018-10-24_15-33-05.png

 

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
7 - Meteor

 

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
ACE Emeritus
ACE Emeritus

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.

2018-10-24_16-20-52.png

 

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

 

CodingMan
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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
7 - Meteor

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?

input1.PNG

 

 

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.

Action.PNG

 

 

       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
7 - Meteor

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)  

 

       input1.PNGinput1.PNG

 

 

 Capture.PNG

 

jdunkerley79
ACE Emeritus
ACE Emeritus
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
7 - Meteor

Hello,

 

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

Labels