Data transformation help needed

[Andrew_]

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:

001	002	003
a	b	c
a	c	c
b	a	a

And this is what I want it to look like:

Time	a	b	c
001	2	1	0
002	1	1	1
003	1	0	2

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. 

[atcodedog05 · Accepted solution]

Hi @Andrew_ 

Here is a workflow for the task.

Output:

Workflow:

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]

Hi @Andrew_ 

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

Thanks,

Philip

[PhilipMannering]

Yeah... what @atcodedog05  said...

[Andrew_]

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