topic Re: How to transform this data in Alteryx Designer Desktop Discussions

How to transform this data

AmitBelagali — Mon, 25 Jun 2018 13:04:03 GMT

Hi All,

We have a scenario wherein we have few rows that needs to be transformed as columns. Please find attached screenshot. In this, we have Region, Sector, Filter, Metric Filter, Population and Brand as rows and below that we have data as column. We need to make all these as columns with date.

Also, like this we have around 10-12 sheets with same structure.

Could you please help us on this?

I have attached both input and output file screenshot.

Thanks,

Amit Belagali

Re: How to transform this data

LordNeilLord — Mon, 25 Jun 2018 13:02:17 GMT

hey @AmitBelagali

There are no screens attached.

But have you looked into the crosstab tool?

Re: How to transform this data

AmitBelagali — Mon, 25 Jun 2018 13:18:18 GMT

Hi @LordNeilLord,

Tried that Neil. But in vain. Could you please suggest us a different way?

Re: How to transform this data

LordNeilLord — Mon, 25 Jun 2018 13:22:30 GMT

Ewww, that looks like one messy spreadsheet!

Without an actual example of the data it's going to be hard to give you a full solution...but looking at it from the pics...this is probably going to need some muli-row formula tools and a few transposes first.

Can you attach an example of the actual data (or that mock up you just screen shotted)?

Re: How to transform this data

AmitBelagali — Tue, 26 Jun 2018 08:18:59 GMT

Hi @LordNeilLord,

Please find attached mockup data.

Thanks for your help.

Thanks,

Amit Belagali

Re: How to transform this data

LordNeilLord — Tue, 26 Jun 2018 07:59:31 GMT

Hey @AmitBelagali

Have you tried to import your mockup into Alteryx?

I can only get to import the first 8 rows

Re: How to transform this data

AmitBelagali — Tue, 26 Jun 2018 09:39:28 GMT

Hi @LordNeilLord,

I have tried loading the file. The file loads fine. In the Preview only first 8 rows are loaded don't know the reason why. But i am able to load the file.

Thanks,

Amit Belagali

Re: How to transform this data

LordNeilLord — Tue, 26 Jun 2018 10:16:07 GMT

Hey, yeah I got it loaded

Re: How to transform this data

AmitBelagali — Tue, 26 Jun 2018 12:15:43 GMT

Awesome. Could you please me with further steps?

Re: How to transform this data

LordNeilLord — Tue, 26 Jun 2018 12:29:31 GMT

Hey @AmitBelagali

Sorry for the delay

I've attached an example of how to approach one of the sections, mostly reliant on Multi-Row formula. Use this to get you started and see if you can figure out the rest :)

Re: How to transform this data

AmitBelagali — Fri, 29 Jun 2018 07:45:57 GMT

Hi @LordNeilLord,

Thanks a lot for your help. We cracked all the further steps by using your workflow. You are a rockstar.

Thanks a lot again.

Thanks,

Amit Belagali

Re: How to transform this data

MarqueeCrew — Fri, 29 Jun 2018 08:01:33 GMT

@AmitBelagali

I second your nomination of @LordNeilLord to rockstar status. I also think that kudos are in order for your contribution to the community with a post asking for assistance as well as your recognition for Neil's help.

Cheers,

Mark

Re: How to transform this data

LordNeilLord — Fri, 29 Jun 2018 08:03:44 GMT

If only more people like @AmitBelagali were brave enough to ask for help :)