Hi all, I am creating a workflow using an external excel. The original excel has rows with dates. Dates change everyday (we always have a new day in the excel). I need to convert that data in columns and the workflow must select all days dynamically. I should have one column for each day. Please advise, thank you so much for the help!
Original excel:
Date | amount |
01-Aug-2022 | 400 |
01-Aug-2022 | 300 |
02-Aug-2022 | 50 |
08-Aug-2022 | 500 |
08-Aug-2022 | 100 |
What is needed:
01-Aug-2022 | 02-Aug-2022 | 08-Aug-2022 |
700 | 50 | 600 |
Hey @Camiiara, you can use the Cross-Tab tool to achieve this. When configuring, you need to select an aggregation which in this case would just be sum:
I've then just added a Dynamic Rename tool to clean up the name back to the original format:
Hey @Camiiara,
The cross tab tool is what you need:
This solution uses the cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
Hey. thank you so much. Could you please help me with the configuration of Dynamic rename tool? in order to select all dates automatically (everyday dates change)
thank you so much! quick question. i tried to do it but if i have new dates everyday, they do not appear automatically. Only dates that i have selected on summarize tool. How can i do it? thanks!
@Camiiara in the Dynamic Rename tool, just make sure you have Dynamic or Unknown Fields selected. That way, if you add more dates, this applies the Formula to extra fields that pop up. As shown here - I've manually added data for 15-Sep-2022 to the Text Input without touching anything else and the change automatically carries through:
The Summarize beforehand isn't necessary as this is just used to aggregate which you need to do within the Cross-Tab tool itself anyway.