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

Repeating Macro for Multiple Formulas

_collin_lowney
7 - Meteor

Hi All!

 

I am struggling to figure out how to automate the need to create multiple formulas.  My current data looks as follows:

 

ProjectSIGNAL ASIGNAL BSIGNAL C
A101
B000
C101
D100

 

I will need to create multiple formulas for each 'Signal'.  For example if I was to do this manually I would created the following formulas for Project A:

1) "SIGNAL AB" = If [SIGNAL A]+[SIGNAL B]=2 THEN 1 ELSE 0

2)  "SIGNAL AC" = If [SIGNAL A]+[SIGNAL C]=2 THEN 1 ELSE 0

3)  "SIGNAL ABC" = If [SIGNAL A]+[SIGNAL B]+[SIGNAL C]=3 THEN 1 ELSE 0

 

I need to repeat this for each project and each 'Signal' Test.  Is there a simple way to set this up with a Macro?

 

Any help would be much appreciated!!

 

7 REPLIES 7
NicoleJohnson
ACE Emeritus
ACE Emeritus

I have a leading question, but I did find one solution that might work... Does the number of SIGNAL combinations you're looking to validate change? Or will it only be those 3 you specified?  If so (or if you can specify the SIGNAL combos ahead of time in a text input, as I've done in the workflow attached), then the following might work.

 

1. Transpose Data (Key Field = Project)

2, Append a list of the SIGNAL combos you're looking for to every row of data in the transposed list

3. Formulas to calculate:Whether or not the Name column contains the letters in the SIGNAL column that was appended... and a column to find the check value to look for based on the number of letters contained in the SIGNAL name (i.e. SIGNAL AB = 2, SIGNAL ABC = 3)

4. Filter for only those rows that have a Name containing letters from the SIGNAL column

5. Summarize Tool to Group by Project, SIGNAL, check value, and Sum of Values column

6. Formula to show 1 if the Sum of the Values = Check Value

7. Cross Tab tool, Group By Project with SIGNAL as header and Value column as Values

 

I believe this would give you the results you're looking for... but if not, perhaps a little more background info might help? :)

 

Good luck!

NJ

 

_collin_lowney
7 - Meteor

So to give some more background, the # of Signal combinations will increase, so right now we have 30 and will go up to 100, which would not allow us to use the Append Tool unfortunately.  

 

Also of note, the values for each Signal field will only be 1 or 0.  There are about 30k+ 'Projects' as well

 

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

I think this version will be a bit more dynamic. As long as you have a list of your desired signal combinations, it should do the rest for you, and the join works better than the append. I tested it with about 10K rows of fake data and 30 combos, and it completed in 6 seconds, so I don't believe performance will be an issue?

 

Let me know what you think. It's a bit of a squirrelly, long-winded workflow with a bunch of Transpose & Cross-Tab tools, but it should do the trick. :)

 

(Basic path: Parse out the signal components in the input data and the list of signal combos, join together with the assistance of Transpose & Cross-Tab, and eventually join the number of signal characters to the re-oriented data to determine if the sum of values for each signal combo by project = the number of digits required for each signal combo.)

 

NJ

_collin_lowney
7 - Meteor

Thanks Nicole - Any possible suggestions on how to generate the Signal Test combination list?  is there a way to automate all the possible combinations up to with up to 3 Signal tests?  So to automate building out the list of SIGNAL ABC  SIGNAL ACD, etc?

NicoleJohnson
ACE Emeritus
ACE Emeritus

Ok, it isn't pretty, and I'm positive it could be streamlined... but I believe this workflow will do it. Tested it for letters A through J and it appeared to work (generates 165 unique 2 & 3 letter combinations for letters A-J, unique meaning that it doesn't duplicate SIGNAL AB and SIGNAL BA, just shows results for one of them). Give it a shot, let me know if it works? This was a fun one... :)

 

NJ

_collin_lowney
7 - Meteor

Thanks so much for the help!  I made some tweaks and was successful with running it against 30k projects and 30+ signals

_collin_lowney
7 - Meteor

Any suggestions as to if I wanted to add a dash "-" between the signal letters as to be able to identify the difference between SIGNAL AAB as either SIGNAL A-AB or whether it should be SIGNAL AA-B?  

 

I have played around with the parsing but stumbled when it came to joining both back together

Labels