Dynamic Calculations

[CrayonZac]

Hi experts,

I have a question about Dynamitic Calculations. Please have a look at the following tables.

records	Dec-17	Dec-18	diff
1	100	200	100
2	300	410	110
3	124	988	864
records	Dec-18	Dec-19	diff
1	200	400	200
2	410	800	390
3	988	1000	12

The first table is the current one, and the next year, the field names are different. I have Dateformat to change the Names, and I was wondering is there someway to calculate diff next year, by (column 3 - column 2), rather than Dec-19 - Dec-18

Thanks a lot

[JohnJPS]

Hi @CrayonZac,

Assuming "diff" will be calculated by the workflow and is not part of the input (e.g. input is the first three columns in both instances, and you want the same workflow, without changing it, to generate the "diff" column based on column position rather than column name).

If so, there should be multiple approaches, but one way is to Transpose your data to columnar format, then count the columns use a MultiRow formula, then Cross-Tab back using the count rather than the original column name... then use a formula for the "diff" based on that:

See the attached workflow as well.

(You can almost see a simple calculation macro waiting to happen.  :-)

Hope that helps!

John

[CrayonZac]

Thanks John,

I was wondering is that possible I can get the Columns Index? And then I can do [field_1] + [field_2] - [field_3]?

Thank you.

[JohnJPS · Accepted solution]

I doesn't appear so, with simple tools out of the box.  The transpose/cross-tab trick in my workflow gives you columns by number, so that my Formula tool is just doing [2] - [1].

Also, the R language allows accessing columns by number, so the following is a very simple approach:

df = read.Alteryx("#1", mode="data.frame")
df$newField = df[,1] + df[,2] - df[,3]
write.Alteryx(df, 1)

(That would be field1 + field2 - field3.)

[CrayonZac]

Sir, according to your Transpose and CrossTab function, I have the following table:

Name	Value
Dec_16_CY	718.009
Dec_17_CY
Jun_16_CY
Jun_17_CY
Jun_18_CY
Dec_16_CY	1436.018
Dec_17_CY
Jun_16_CY
Jun_17_CY
Jun_18_CY
Dec_16_CY	2872.036
Dec_17_CY
Jun_16_CY
Jun_17_CY
Jun_18_CY
Dec_16_CY	718.009
Dec_17_CY
Jun_16_CY
Jun_17_CY
Jun_18_CY
Dec_16_CY	718.009
Dec_17_CY
Jun_16_CY
Jun_17_CY
Jun_18_CY

I was wondering, how could I calculate Dec_17 + Jun_18 - Jun_17? this is the last 12 month value.

Thanks a lot

[BenMoss · Accepted solution]

There is actually an extremely easy method to get the column number in Alteryx through using the input and dynamic rename.

I've attached an example. 

Personally I would have gone with @JohnJPS's tranpose approach but this works two but it's less dynamic I feel.

Working with your data in one column is always easier than working with data across multiple columns.

[CrayonZac]

Thanks Ben,

The thing is that I just post an example here, I need to calculate by 3 columns, [field 1] +  [field 2] - [field].

Could you please see my new updates above?

[JohnJPS]

Hi @CrayonZac,

I believe either of my approaches (Transpose, or R) or @BenMoss's approach (Field Info) will work regardless of number of columns: if you want to reference the nth column, its name will be [n] following the bits of code provided.  At that stage, you can employ a simple Formula tool of arbitrary complexity, accessing any number of columns by their associated "column number."

[CrayonZac]

Thanks Ben, Dynamic Rename do make it working.

[CrayonZac]

Thanks John,

I do like R Tool to get this done. But I can not get R in Alteryx at this time. I used Dynamic Rename to rename field names, make the calculation and join back t original table to get the final table.

But R must be a better way to do that.

Regards,