Transpose columns into multiple rows

deona720

I have this data set:

Member ID	23-Jan	23-Feb	23-Mar	23-Apr	22-Jan	22-Feb	22-Mar	22-Apr
Member ID	123	23-Jan	33	23-Feb	41	23-Mar	23	23-Apr	23	22-Jan	32	22-Feb	32	22-Mar	34	22-Apr	34
Member ID	456	23-Jan	4	23-Feb	15	23-Mar	15	23-Apr	15	22-Jan	6	22-Feb	6	22-Mar	2	22-Apr	2
Member ID	789	23-Jan	2	23-Feb	4	23-Mar	8	23-Apr	8	22-Jan	8	22-Feb	4	22-Mar	8	22-Apr	12
Member ID	111	23-Jan	24	23-Feb	24	23-Mar	26	23-Apr	26	22-Jan	27	22-Feb	22	22-Mar	27	22-Apr	27
Member ID	222	23-Jan	3	23-Feb	12	23-Mar	12	23-Apr	13	22-Jan	14	22-Feb	14	22-Mar	12	22-Apr	12
Member ID	333	23-Jan	2	23-Feb		23-Mar		23-Apr	1	22-Jan		22-Feb		22-Mar	1	22-Apr
Member ID	444	23-Jan	2	23-Feb	2	23-Mar	2	23-Apr	5	22-Jan	6	22-Feb	8	22-Mar	10	22-Apr	11

The date columns represent the amount of logins to their account by month. I want to be able to consolidate this data into one column so that I can put this into cards more easily. I assume there would be \ 33 rows for member "123" that say Jan 23, and then 41 rows for the same member that show Feb 23, etc. This will be a monthly update, so the less manual labor the better.

How can I combine these columns into 1? Thanks in advance!

GrantSmith

You can utilize Magic ETL and the Dynamic Unpivot tile to take your columns and convert it to rows.

Here's more information: Customer Support Community

ST_Superman

@deona720

Just to be clear, using the Dynamic Unpivot tile in ETL will not quite get the results you described. You would end up with three columns:

"MemberID" | "Date" | "Value"

123 23-Jan 33

You would not get 33 rows for member 123 in Jan.

GrantSmith

You can utilize Magic ETL and the Dynamic Unpivot tile to take your columns and convert it to rows.

Here's more information: Customer Support Community

MichelleH

@deona720 I suggest using the Dynamic Unpivot column in MagicETL to accomplish this. You can read more about how the tile works here: Customer Support Community

Edit: Grant beat me to it!

ST_Superman

@deona720

Just to be clear, using the Dynamic Unpivot tile in ETL will not quite get the results you described. You would end up with three columns:

"MemberID" | "Date" | "Value"

123 23-Jan 33

You would not get 33 rows for member 123 in Jan.