Dynamically Reference Columns with and output with formulas

[PaladinKnight]

I'm trying to dynamically reference some columns and make them formulated. 

I have a dataset for example

Column A1	Column B2	Column C3	Column D4	Column E5	Column F6
Apple	1.1	0	dsfdsf	100	Red
Banana	4.5	dasdasd	dsadsad	20	Yellow
Pear	1.6	dasdsa	dsadad	30	Green
Orange	2.1	dasda	adasdsad	40	Orange
Grape	1.8	adasdsad	asdsadsad	50	Purple

What I'm trying to do is that, I'm trying to make a workflow so that my output is column B2 and E5 but both are multiplied by 1000. 

Furthermore, I am trying to make sure that when I make the workflow, I want to tell Alteryx not to refer to column B2 and E5 as the columns names as they are because their locations can change in other files, I get. In other words, I want to tell Alteryx to "get the data from the second column multiply it by 1000 and then get the data from the second last column and multiply it by 1000." The columns won't always be named "Column B2" and "Column E5", but they will be positionally the same in other files hundreds of other columns (second from the start, second last and so on).

I want my output to be something like this:

Fruit	Weight 1 (B2*1000)	Weight 2 (E2*1000)
Apple	1100	100000
Banana	4500	20000
Pear	1600	30000
Orange	2100	40000
Grape	1800	50000

Any guidance would be appreciated, thank you

[Prometheus · Accepted solution]

@PaladinKnight If your input is Excel, you can configure the Input Data tool to basically push your headers down into the first row of data and the default field names will be F1, F2, F3, etc. After that, you can add a Sample tool and skip the first row to get rid of the old headers, use a Select tool to change the data type of F2 and F5 to Double or other numeric data type, then use a Formula tool to multiply F2 by 1000 and F5 by 1000. Then end with a Select tool to deselect unnecessary columns.

 

 

 

[PaladinKnight]

The file does not open - which version did you use? Wanted to ask as well if instead of "F5*1000" there is way to dynamically refer to it as "second last" because F5 might be F7 in one file, F10 in another. Thanks!

[SPetrie]

This is a variation of a process I use to get specific columns.

Use a field info tool to list all the fields in the file.

From there, a sample tool to grab the rows with the first two column names and a second sample tool to grab the last two.

Join them in the specific order and then grab just the first three rows. You now have the names of the columns you care about.

Next, a formula tool to make a new column to identify that we want to keep them. Using a dynamic rename tool, I update the descriptions of those specific fields, and then a dynamic select tool with a formula selects only the fields that have "keep" as a description. Now we are left with only the three columns we care about.

In a parallel branch, I skip the first row and then create a text version of a formula that uses the original column name and multiplies it by 1000.

Using a dynamic replace tool, I update the formula for the columns with that new formula we created.

And we are done. No matter how many columns or what their names are, you will always have the 1st, 2nd, and 2nd from last columns and they will get multiplied by 1000

[Prometheus]

@PaladinKnight The version is 2022.1.