Column name changes everymonth or quarter

[pokhan27]

hi Expert

i created a yearly reporting flow based on quarter but due to error in name the value is stuck in a flow. Is there any way to make the column name dynamic which changes according to the month.

 

[apathetichell]

Two ways - 1) use a macro and transpose this beforehand. Your new column will now be [name] and your sum column will be [value] (normal way).

2) update the column you are summing by using a batch macro into your summarize tool where you change the field you are summing. very difficult. if syntax is off your workflow will crash.

I'd say stick with one. make sure you are transposing your dynamic columns and make sure things like branch/region name are key columns.

[pokhan27]

Do you have any sample as i am new to this macro? 

[shreyanshrathod]

The Dynamic Rename tool allows you to rename a column name dynamically.

It supports various configuration modes - 

            1) FORMULA - you can write a formula to give a column name. (In your eg, you can use Datetime functions to get year and quarter and rename column.

If you can also share the desired output,  I can build a sample workflow for you.

Regards,

Shreyansh

[danilang]

Hi @pokhan27 

Here's a sample that demonstrates two patterns that you can use.

1. If your input has varying numbers of value columns, use a Transpose/Crosstab pattern

The config of the transpose looks like this

As long as you have the Dynamic or Unknown Columns checked any new columns will be automatically included in the transposed columns.  In the CrossTab tool, group by the same columns you used as keys in the Transpose tool, use Name as the Column Headers and Value as the Column Values using Sum as the Method

Your output will look like this 

2. If your input has a specific column that can change names over time use a dynamic rename pattern

Use a Field Info tool, a Record ID and Formula tool to the build a list of the existing column names and a list of new names, renaming the target one to [_SummaryColumn_].   Use a Dynamic rename in "Take Field Names from Right Input Rows" mode to rename the target.  Apply the Summary to the [_SummaryColumn_] and use a second Dynamic Rename to restore the original field names

Dan

[pokhan27]

I did do that in the transpose way. 

just sharing you a sample flow. I am having issue in while summing.

[danilang]

Hi @pokhan27 

Apply the final cross tab after your summarize tools instead of before.  In the summarize tools, Group By Region Name and Quarter.  You can also combine the two summarize tools into one like this 

Do the cross tab after this

Dan