Looking to feed a bunch of formulas through the formula tool

[SnareRiff]

Hello!  I have done some googling but couldn't quite find an answer to my question, so I was hoping you smarties could help me out.  

Basically, what I do is I am doing a simple cell comparison between two files, one PROD and one UAT.  I copy and paste the column names into excel, and then I use concatenate to create both the Formula names as well as the actual formula itself. 

Here is what my excel sheet looks like:

I then create a new formula for each row and I copy and paste the FORMULA_NAME into the "+ Add Column" to name the new column, and then I copy and paste the FORMULA into the actual meat of the formula.  

I was wondering if there was a smarter and easier way to do this, as when I get 150 columns this can take a while.  I have tried using a batch macro and I can't quite seem to get it to work. 

What I would like to have happen is I would create this Excel sheet, and then input it into the Alteryx flow, and then that combined with actual input file I run the tests on, it would spit out my output which would include all the newly created test columns. 

Is this possible with Alteryx?

Thank you!

[Carolyn · Accepted solution]

Yes! I've done comparisons with millions of rows x dozens of columns

What I do is: 

1. Import both data sets
2. Use a Dynamic rename to add "UAT" or "PRD" at the end of each column
3. Join the data sets on my unique identifier
4. Use a Transpose tool to get one giant list of everything
5. Sort by Record ID then Column Name. This will make it so ColumnA_PRD is followed by ColumnA_UAT then ColumnB_PRD then ColumnB_UAT. 
6. Multi-Row formula to check if the columns match
   1. For this, I'm checking to see if my column ends with "PRD" (meaning the next is the "UAT" one) and the Value in the row equals the Value in the next row - that's a "Match"
   2. If the Value in the row does not equal the Value in the next row - that's a "Fail"
   3. If my column name does not end with "PRD", meaning it's the "UAT" one, I don't want to compare it against the next column, so I just put null(). Then in the next step, I can filter to exclude the nulls which will get rid of half of the rows leaving just the Match/Fail rows
7. Filter for all of the comparison results or if you only care about Mismatches, filter for "Fail"
8. Rename the column Name to have "Comparison" at the end instead of "PRD" so it's clear that it's actually the Comparison results

Give this a try and if it resolves your issue, please mark the post as resolved!

[SnareRiff]

Thank you, this is great!  I am always amazed how there are multiple ways to do the same thing in Alteryx. 

[Carolyn]

The 2 tricks I found:

1. Add the "PRD" / "UAT" / "SND" (Sandbox) labels at the end of the column name, not the beginning. That way, when you sort them, the PRD/UAT ones sort together. My first version had PRD_ID, PRD_TCV, SND_ID, SND_TCV and none of those sorted together which didn't work for me. 

2. Keep the same number of characters at the end. I can't remember why I was doing it, but when I had "_PROD" and "_SND" and when I was removing the suffix, I couldn't easily use the LEFT formula because of the different numbers. 

But once you get it working how you want, it's really spectacular!

[SnareRiff]

Yup those are exactly the same things I figured out as well.  When I first wrote this compare flow, I ended up having to change the prefixes to suffixes just for the reason you mentioned, for easier sorting.

Also, I took your solution and implemented it with my data but used _PROD and _UAT, and when I went back to remove the suffixes i found it much easier if I just turned PROD into PRD, as then I could use the Left function a lot easier.  

I always pride myself on being able to think outside the box, and I am excited to get my hands dirtier into Alteryx as it seems that once I learn more about more of the tools I will be able to think more outside the box for solutions rather than just the same 8ish tools I use.  

Thanks for your help!