dynamic summarize

[spetkae09]

Hey Everyone,

Every month I am provided 12 months of data. Each month the 13th month back drops off the report and I am left with totals for Current 12. That means the column headers change so i need a way to dynamically change the columns that i am summarizing in the summarize tool.

I attached sample report to show in the first 12 months i summarize and total each month in the second report all the summarize fields are incorrect. the last one is missing and the most recent needs to be added. 

Each month id like to use directory tool to select most current month file and have the summarize be dynamic so i do not have to go into the workflow and change the months within the summarize.

Appreciate any help thanks!

[T_Willins]

Hi @spetkae09 

Transpose, summarize, cross-tab.

[NickSm]

Hey @spetkae09 

To make a summarization a little more dynamic, you can use a combination of the transpose and cross-tab tools.  With this configuration you don't have to hard-code any column names into the calculations, the pivot will happen with whatever date fields come in.

Example attached

[NicholasM]

Hey @spetkae09 

I believe this can be accomplished dynamically using a series of tools. One Transpose, and one Cross Tab. See photo and workflow attached. 

[spetkae09]

with each monthly change do i need to manually change the check marks in the transpose each time the months change and drop off and new ones appear?

[T_Willins · Accepted solution]

No because Alteryx makes it easy.  Just make sure "things to count" is unchecked (keeps it out of your data columns) and "Dynamic or unknown columns" is checked (brings in new fields each month).  On my workflow I attached I just copied and pasted the tools to the 2nd input.

[spetkae09]

thank you!

[rafalolbert]

Hi @spetkae09,

Proposed solution is to:

- use the 'Field Info' tool first to get the metadata

- get the id for every field (i understand you'll have equal number of ids every month when you run your workflow)

- dynamically rename columns and the 'Summarize' tool will now be working as expected

- reverse engineer the rename to get the dynamic names as per the new dataframe

Thanks,

Rafal

[Purpose]

Hi Rafal, 

Any ideas for situations when the number of ids varies from one feed to another feed into the Summarize tool?  I thought I could keep extra fields just in case it is necessary but Summarize tool gave an error when I had fewer fields than the tool was configured for:  "The filed does not exist..."

_______________

- get the id for every field (i understand you'll have equal number of ids every month when you run your workflow)

Purpose