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

Data transformation help needed

Andrew_
5 - Atom

Hi all, I need help with transforming a table into something that would be more useful for me. I probably need to use the summarize, cross tab and transpose functions but I'm unsure of the order in which to apply them. 

 

Here's an example of the layout of the data that I currently have:

 

001002003
abc
acc
baa

 

 

And this is what I want it to look like:

 

Timeabc
001210
002111
003102

 

What I want to do is create a table that tells me how many times a variable occurred (a ,b, c) during a specific time period (001,002,003). 

 

In my actual data set, I have 15k+ rows, 144 columns and around 200 variables. The final result would give me 144 rows and 200 columns which would be easier to use. 

 

Thanks in advance and let me know if you need anything else from me. 

4 REPLIES 4
atcodedog05
22 - Nova
22 - Nova

Hi @Andrew_ 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1606155456829.png

 

Workflow:

atcodedog05_1-1606155467612.png

Steps

1. Transpose

2. add column count with value 1

3. cross tab Name as key values as count name and count as value with aggregation sum.

4. data imputation to replace Null() with zero over all numeric columns.

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

PhilipMannering
16 - Nebula
16 - Nebula

Hi @Andrew_ 

 

You need the Transform tools. Take a look at the solution I've provide you attached.

PhilipMannering_0-1606155494350.png

 

Thanks,

Philip

PhilipMannering
16 - Nebula
16 - Nebula

Yeah... what @atcodedog05  said...

Andrew_
5 - Atom

Thanks for the help. I applied it to my actual data set and it worked perfectly!

Labels