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

SOLVED:Need Help to Get Data by Individual Dates from Calendar Matrix in Excel Spreadsheet

taxtechfl
7 - Meteor

I am looking for assistance to convert a set of data in a spreadsheet that is arranged in columns going across by month and day in a calendar format.  The month is a row above the day of the month.

 

I have tried a couple of different approaches, but there is likely a much easier way with fewer steps.  If anyone can help with this, I would be greatly appreciate it.  Thank you.

 

Below is a screen shot of how the data set is arranged.  

Calendar Matrix Screen Shot.png

 

The desired result is to have the values for each record corresponding to each date.  A sample of the results for Entity1 would be something like the following:

EntityDateValue
Entity12020-01-01 
Entity12020-01-021
Entity12020-01-031
Entity12020-01-041
Entity12020-01-051
Entity12020-01-06 
Entity12020-01-07 
Entity12020-01-081
Entity12020-01-091
Entity12020-01-101
Entity12020-01-11 
Entity12020-01-12 
Entity12020-01-13 
Entity12020-01-14 
Entity12020-01-15 
Entity12020-01-16 
Entity12020-01-17 
Entity12020-01-18 
Entity12020-01-19 
Entity12020-01-20 
Entity12020-01-21 
Entity12020-01-22 
Entity12020-01-23 
Entity12020-01-24 
Entity12020-01-25 
Entity12020-01-26 
Entity12020-01-27 
Entity12020-01-28 
Entity12020-01-29 
Entity12020-01-30 
Entity12020-01-31 
Entity12020-02-01 
Entity12020-02-02 
Entity12020-02-03 
Entity12020-02-04 
Entity12020-02-05 
Entity12020-02-06 
Entity12020-02-071
Entity12020-02-081
Entity12020-02-091
Entity12020-02-10 
4 REPLIES 4
fmvizcaino
17 - Castor
17 - Castor

Hi @taxtechfl ,

 

This is the best I could build. Merge columns is not possible in Alteryx without using a complex workflow and reporting tools.

 

Best,

Fernando Vizcaino

taxtechfl
7 - Meteor

Thank you, Fernando (@fmvizcaino) , for developing the workflow for me.  That can come in handy for me to work in both directions with this data.  Unfortunately the original data is the one that is arranged going across by month and day in the horizontal calendar format, meaning the rows of dates and values by entity is the end result desired rather than the starting point (input data).  I apologize for the misunderstanding.  I hope this helps clarify.

fmvizcaino
17 - Castor
17 - Castor

Sorry @taxtechfl , my bad! 

 

Here it is.

fmvizcaino_0-1600351214391.png

 

Best,

Fernando Vizcaino

 

taxtechfl
7 - Meteor

@fmvizcaino Fernando:

Thank you for the solution, and thank you so much for doing this so quickly!  I really appreciate it!

Labels