How to rearrange the data

[manishab]

Hi Alteryx Community,

I have data in this format. 

And I want it to transform to be in this format.

I would really appreciate any help, I have tried using a transform tool but it's only doing part of the job and I don't know how to get the Geography, Description, Source and put them in columns as I have it in my 2nd picture.  

I have attached the excel's. 

Thank you 

[bpatel]

Hi @manishab ,

i don't think the excel files were attached to the post. would you please try again?

[manishab]

Hi bpatel,

I have attached the excel's now.

[ponraj · Accepted solution]

Here is the sample workflow.  Hope this is helpful. 

[Nezrin]

Not sure how the date and values are related. but try this :

1) sample (first 5 records) -> transpose -> cross tab

2) sample (skip first 5 records) -> transpose

join 1 and 2

[manishab]

Hey Punraj, 

Thank you so much for taking your time and creating the workflow. 

I am using the Input Data tool and bringing the data but it's not working. I do see that in the workflow that you have provided you're using input text where the Field1,2,3 is being generated and it's easier to transpose and cross tab the data. But it's not working when I use Input data tool. I am sure there is a way to include the rows and follow along with your solution but I am not able to figure it out. 

Would you be able to help me, please?  

[ponraj]

have option "first row contains data" checked while reading the data using input data tool. 

[manishab]

Thank you Ponraj that helps.