Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

dynamic summarize

spetkae09
8 - Asteroid

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.alterxy pic.png

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!

8 REPLIES 8
T_Willins
14 - Magnetar
14 - Magnetar

Hi @spetkae09 

 

Transpose, summarize, cross-tab.

 

Month test.JPG

 

NickSm
Alteryx
Alteryx

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

 

pivot_sum.PNG

NicholasM
Alteryx Alumni (Retired)

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. 

 

Capture.PNG

spetkae09
8 - Asteroid

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
14 - Magnetar
14 - Magnetar

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
8 - Asteroid

thank you!

rafalolbert
ACE Emeritus
ACE Emeritus

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

 

image1.JPG

 

Thanks,

Rafal

Purpose
7 - Meteor

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 

Labels