Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Need to apply same formula for the fields incoming from two different excel sheets


Hi All,

I am having two different incoming excel sheet connections with the same fields in both the sheets, shown in example below



ID, Sales1, Sales2, Sales3, Sales4



ID, Sales1, Sales2, Sales3, Sales4


I am joining these two excel sheets on ID column, now I want a formula which compares both the sheets and gives a value, shown as an example below;


Comparison formula:


Comp_Sales1:  Iff(Excel1_Sales1 - Excel2_Sales1) > 1 Then "Out of Range" Else "In-Range"


Similarly, I want the same formula for all the other columns as well, that is sales2, sales3 and sales4. 


Can someone guide me through this? Here are the sample excel sheets with the sample data in it.





Alteryx Partner

I would do this ... 


2019-01-06 10_11_06-Alteryx Designer x64 - Comparison formula.yxmd.png


Join the files together based on ID.  Because the columns are identical names, Alteryx renames all the files on the right side to Right_


Add a formula tool with 4 separate calculations to do the in/out calculation


You can then remove the numeric fields if you want and continue on :) 



I have already thought of that solution, but this is just the sample data, I have about 85 columns coming in from both the files, I cannot create 85 formulas.. So, I am looking for some more dynamic solution.

Sorry, I should have mentioned that in my post before.


Alteryx Certified Partner
Alteryx Certified Partner
I'm not going to give you the answer but look at the transpose tool to bring all of your columns into a single column with many rows. Then you can work with just a single formula tool.


I did the transpose logic, which brought me all the columns into one single column "Name" and Values of them are stored in "Value" column. But my data is huge, its having about 118 columns which about 400K rows. I just did a sample test for 10k rows and it took like 15 mins to process them. I don't want to image how much time would it take if I process all the records in one go. Is there a better solution to this?