community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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

Asteroid

Hi All,

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

 

Excel1:

ID, Sales1, Sales2, Sales3, Sales4

 

Excel2:

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.

 

Thanks!

 

 

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 :) 

Asteroid

Bolide,

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.

Ben
Asteroid

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?

Labels