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

How to leverage alteryx to apply complex formulas

Meteoroid

I am very much new to alteryx & has started to work on it for a certain project. I am stuck at problem.

 

Description:

In case of excel, suppose if I am applying IF Function like below

 

IF Condition then True Else False

In case of condition we refer a certain column of particular excel-tab/excel-workbook 

then use a true value in which again we can refer to certain columns of particular excel-tab/excel-workbook

followed by false

 

in excel we have multiple tabs or workbooks which can be referenced in the formula.

 

Problem/Objective to achieve:

 

I want to do same thing in Alteryx where I have different input data (say 3 different input files) & then I want to add new

columns to one input data with reference from some other input data files & I don't have any common fields to join them. It is like creating a new table with multiple conditional columns having references from different tables.

 

How that can be done?

 

 

Any help is highly appreciable. Thankyou. 

 

 

Please read my first response to mmenth, that would help you to understand the exact problem that I am looking forward to solve. 

 

 

 

 

Bolide

Hi @anshuman7,

 

Great question, I think you do have to think a little bit differently about this type of problem in Alteryx as opposed to Excel. Since you say there are no common fields to join, it sounds like you might be wanting to summarize a whole column (or multiple) in one of your three input files and apply it to the remaining input files. I would do this in Alteryx by utilizing the summarize and append tools.

 

I made some example data here to show you how this process works:

Capture.PNG

 

In this example, let's say we want to add two condition columns to our main data stream, where If the sum of a field in input file 1 > 10 then TRUE, else FALSE; and if the avg of a field in input file 2 <10 then TRUE, else FALSE.

 

There are two ways you can do this. The first way is shown with input file 1, where I am summing the field I want with the summarize tool, then appending that single stat to my main data stream. I then make a formula tool that actually holds the if statement. This applies the condition to every row in our main data stream.

 

The second way is similar, but you can do the formula tool before appending it to the main data stream. So rather than appending a number, we are appending the TRUE or FALSE of our if statement. 

 

I've attached the example workflow here so you can see how it runs. This is a very basic example which you might want to make more complex, but this is a good start to how I would recommend thinking about this type of problem.

 

Best,

mmenth

 

 

Meteoroid

Hi@mmenth

 

Thanks for your response. 

 

I looked at the workflow shared by you, the thing is you are also basically appending two different input files into a single file & then applying the functionality on it. 

 

I have a bit more complex problem that I am looking forward to solve. 

Sharing it below. 

 

I have three files file1,file2,file3 in excel format. 

Now I want to generate extra columns in file 1 by taking references of columns from all three files.

 

For the start say I want to create column named VAR1, which should be created according to following logic

 

IF file1.Value = 0 then file1.number1+file2.expenditure

else IF file1.value  =1 then file1.number+ file3.number2

else if file1.value = 2 then file1.number*file3.number2

else0

 

PS: Here when I am mentioning file1.value it means I am referring to column value from sheet1 & similar for other columns from different tables. 

 

Also this is a very basic thing I am explaining you here, the workflow that I am trying to build is quite big & would include large number of files & around 100 columns to be generated in a single file from those multiple ones. 

 

Attaching the excel here, hope you can help. 

 

Regards

anshuman7

 

 

 

 

 

 

 

 

 

 

 

 

Meteoroid

Hi @mmenth,

 

Thanks for your response. 

 

I looked at the workflow shared by you, also executed the same on my machine. In this also you are somewhat trying to append the columns into single file using append tool & then applying function on the same. Although this looks fine, I have a bit complex problem to solve. 

 

Sharing it below. 

 

I have three excel files named File1,File2,File3. 

Now I want to generate multiple columns in File1 by using different formulas & references from the columns present in all files.

 

For example I am explaining one of the scenario below. 

 

If File1.Value = 0 then File2.expenditure + File1.number1

else if File1.Value = 1 then File2.expenditure + File3.number2

else if File1.Value = 2 then File2.expenditure * File1.number1

else 0 

 

PS: here the annotation I am using is File1.Value where File1 refers to the excel file & value to column. It is similar for all the case. I have used this way just to explain the logic irrespective of any language.

 

Also this is just a scenario I am explaining , what I am trying to achieve in the actual project is to generate 100s of columns in a single file, from multiple files with different schemas. 

 

Hopefully you can me help me out with this. 

 

Regards

anshuman7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Bolide

Hi @anshuman7,

 

With the example you provided I was able to just tweak the earlier workflow I gave you. See attached for that update.

 

As far as generating 100s of columns, do you literally have 100s of different formulas you want to apply? If that's the case then I think we are heading more into macro territory and I would suggest making a new post with more specifics of what you're trying to accomplish. 

 

Best,

mmenth

Meteoroid

Hi @mmenth 

 

I went through the workflow shared by you, you are applying a summarize tool & adding the numbers from other two files (file1 & file2) & then appending them to table one followed by the application of the formula tool. 

 

But I don't want to add the summarized value, I want to add the number 1 column to expenditure column with each individual row i.e. number from 1st row of the column adding to number from 1st row of the expenditure & so on. 

Similarly generating the values in the rows from other condition.

 

The condition is as : pasting the exact formula of excel here

=IF(C2>Sheet2!B2,Sheet1!C2+Sheet2!A2,IF(C2<Sheet2!B2,Sheet2!A2*Sheet3!B2,10))

 

I am attaching the single excel file which also contains formula & I have copied the data from 3 different excel files to one excel-workbook containing 3 different tabs, I want to replicate similar kind of formula in alteryx. Please have a look.

 

Will share more details for the actual project I am working on, mean while can you suggest a way to achieve output which I mentioned above.

 

 

 

 

 

 

 

 

 

 

Best

anshuman7

Atom

try this

Bolide

In that case you can just join by record position using the join tool. 

Meteoroid

In this example set I can, but what if the count of records is different in sheets & also the record position is not constant across the sheets & can't be used for joining. 

 

Best 

anshuman7

Labels