Referencing a column Dynamically in Formula Tool

[Lokeshwar]

Hi,

I have a small question -

1- How to reference a column dynamically in a Formula tool. Example - A column with Name "Add 2020" needs to be referenced if I run the workflow this year, In case I run the workflow next year, it should referenced the column "Add 2021". The above referenced column name is then used to derive a Field with Name "CurrDouble" by doing a Mathematical Operation (Multiplication by 2) using a Formula tool  e.g. [Add 2020]*2 for this year and [Add 2021]*2 for next year and so on..

Thanks

Lokeshwar Sinha

[AkimasaKajitani]

Hi @Lokeshwar 

You can use "Dynamic Rename" tool.

Before the Formula tool, you might want to rename the "Add 2021" column to "Add 2020".

For example, if you have a column that starts with "Add", you can rewrite it with the name Add 2020 by Dynamic Rename tool.

The formula is below.

IF StartsWith([_CurrentField_],"Add ") THEN "Add 2020" ELSE [_CurrentField_] ENDIF

This is just one example, but there are many other ways to do it.

[Lokeshwar]

Hi,

I think you misunderstood what I asked.

In the example you attached, there should be two columns - "Add 2020" and "Add 2021". What I need in the output is "Add 2020" value in case I run the workflow this year. In case I run it next year, i should get "Add 2021" column values.

I dont want to overwrite any existing column values by the other.

Thanks

[AngelosPachis]

Hi @Lokeshwar ,

As @AkimasaKajitani suggested, you can achieve this with a dynamic rename tool, although the configuration should be slightly different. You can use a formula tool to create the desired column header via the following expression

"Add"+" "+Tostring(Datetimeyear(DateTimeToday()))

Which will look at the date today and keep only the year part. Then with the dynamic rename tool, you can assign it as a column header

 

Hope that helps. let me know if it worked for you or you have any questions.

Regards,

Angelos

[AkimasaKajitani]

I understand a little more about what you want to do.

According to your first post, you want to change "Add 2020" to the field name "CurrDouble" at 2020.

Is that correct?

If then, the formula is below.

IF [_CurrentField_]="Add "+ToString(DateTimeYear(DateTimeNow())) THEN
"CurrDouble"
ELSE [_CurrentField_] ENDIF

By using the DateTimeNow function, the workflow is automatically renamed using the year when you run it.

It 

If you want to rename it for the latest year, you will need a different workflow.

[Tyro_abc]

Hi @Lokeshwar 

We can do this in couple of ways ..

A: When you have all the future columns present in the dataset. Like you have "Add 2021" , "Add 2022" already available in 2020, then we can just create a flag for every year and use that in calculation.

One way we can do this is by creating a factor field for every measure ( Add XXXX). This factor would be either 0 or 1 based on the "Current" year.  Please find the workflow attached ( CurrentDouble)

B: On the other hand, when you do not have future columns present in the current year, you need to pick up field dynamically based on current year. Here, I am assuming that "Add 2021" would be added only on or after Jan 1st, 2021. This is more dynamic solution. Please refer to "CurrentDouble_v2)

Regards

Arundhuti