How to rearrange the data

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

EconData.xlsx

Basket_2019-12-21_20h_58m.xlsx

Transformation

Accepted answers

ponraj

Here is the sample workflow. Hope this is helpful.

How to rearrange the data.yxmd

All comments

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

Here is the sample workflow. Hope this is helpful.

How to rearrange the data.yxmd

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?

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335