Data Manipulation

ad2045

I have the following data:

User ID	Category	Subcategory
02	A	Sub1
02	B	Sub2
03	C	Syb7

I would like to group the data by User ID as follows:

ID
CategotyA (contains the number of subcategories)
CategoryB (contains the number of subcategories)
CategoryC( contains the number of subcategories)

example output

User ID	CategoryA	CategoryB
02	20	11
03	5	55
04	9	21

Could someone help by providing the process and parameter needed?

IngoRM

Hi,

you can also Turbo Prep to do this sort of transformation very easily.  After loading your data in Turbo Prep, simply switch to "Pivot" and drop the columns from the left into the right places.  "User ID" into "group-by" on the left, "Category" into the column headers at the top, and "Subcategory" into the aggregates at the bottom.  That's it :-)

Hope this helps,

Ingo

Telcontar120

Use the Aggregate operator, and it looks like you want to Group By User ID and Category initially (and aggregate by counting on something like user id, but it doesn't really matter).  This will give you a version of the data you want, only the way you have it, you will need to Pivot the output, where you Group By User ID (again) and Index on Category.
 

IngoRM

Hi,

you can also Turbo Prep to do this sort of transformation very easily.  After loading your data in Turbo Prep, simply switch to "Pivot" and drop the columns from the left into the right places.  "User ID" into "group-by" on the left, "Category" into the column headers at the top, and "Subcategory" into the aggregates at the bottom.  That's it :-)

Hope this helps,

Ingo