Transposing or cross tabbing?

Hi everyone,

Probably an easy fix that I am missing, but, I am really struggling to format a table to use in Alteryx (file attached with two tabs of example).

This is how my table looks to start with:

I would really like it to look like this after it has been manipulated:

Any assistance would be appreciated

Transpose question.xlsx

Datasets

Transformation

Data Investigation

Accepted answers

mceleavey

Hi @Bigmonki ,

this would be first one then the other.

First, Transpose the data grouping by the recordID. Then filter the null values that you produce, so those only populated are required.

Then Crosstab back using the RecordID as the header, and the value as the value, grouped by Name.

This then leaves you one problem:

You are sorting it in a strange way, so use the sort tool to get it sorted as you wish.

Hope this helps,

mceleavey_solution.yxmd

All comments

TheOC

hey @Bigmonki

For this i believe both tools actually need to be used to transform the data:

First , transposing to get the data into a cross-tab ready format:

Second, Cross-tab the data:

Hope this helps, please find attached.

Cheers,
TheOC

Hi @Bigmonki ,

this would be first one then the other.

First, Transpose the data grouping by the recordID. Then filter the null values that you produce, so those only populated are required.

Then Crosstab back using the RecordID as the header, and the value as the value, grouped by Name.

This then leaves you one problem:

You are sorting it in a strange way, so use the sort tool to get it sorted as you wish.

Hope this helps,

mceleavey_solution.yxmd

Bigmonki

Hi Everyone, thank you so much for your support. I have been trying to do this for hours and you guys sorted it out in minutes? I was on the right track but didn't add the finishing cross tab. Your assistance is much appreciated.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703